Combining two fields in SELECT statement
In my table I have a field "firstname" and a field "lastname". I would like to select all records where firstname + space + lastname is a certain value.
I've tried this:
$sql = "SELECT * FROM sam_users WHERE (user_firstname + ' ' + user_lastname LIKE ?)";
But this isn't working. With Google I've found something about using ||, but I don't rea开发者_开发技巧lly understand how I should use that operator. Note that I don't want to use an or-operator (what || is in many languages), but something to concatenate 2 fields (with a space between them) and using a LIKE on that.
Thanks!
With MySQL, you can use CONCAT
:
SELECT * FROM sam_users
WHERE CONCAT(user_firstname, ' ', user_lastname) LIKE ?
or CONCAT_WS
(which ignores NULL values):
SELECT * FROM sam_users
WHERE CONCAT_WS(' ', user_firstname, user_lastname) LIKE ?
However, MySQL won't be able to use any indices when performing this query. If the value of the pattern argument to LIKE
begins with a wildcard, MySQL won't be able to use indices, so comparing to a generated value (instead of a column) won't make a difference.
You can also set the MySQL server SQL mode to "ANSI" or "PIPES_AS_CONCAT" to use the ||
operator for string concatenation.
SET @@sql_mode=CONCAT_WS(',', @@sql_mode, 'PIPES_AS_CONCAT');
SELECT * FROM sam_users
WHERE (user_firstname || ' ' || user_lastname) LIKE ?
This sets the SQL mode for the current session only. You'll need to set @@sql_mode
each time you connect. If you wish to unset 'PIPES_AS_CONCAT' mode in a session:
SET @@sql_mode=REPLACE(@@sql_mode, 'PIPES_AS_CONCAT', '');
MySQL appears to remove any extra commas in @@sql_mode
, so you don't need to worry about them.
Don't use SELECT *
; select only the columns you need.
In SQL the ||
operator does mean string concatenation according to the standard (see SQL 2008: 5.2). The boolean or operator is written OR
in SQL.
However not all databases implement it this way and so the exact syntax depends on the specific database.
- MySQL Server uses the
CONCAT
function. - MSSQL Server uses the
+
operator.
SELECT *
FROM sam_users
WHERE TRIM(Concat(user_firstname, ' ', user_lastname)) LIKE ?;
精彩评论