Combining two fields in SELECT statement

Question

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 really 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!

What RDBMS is this for? And why are you using `LIKE` rather than `=`? — Martin Smith, Mar 13 '11 at 19:16
MySQL, but I'm using the PDO library (PHP) so it should be portable. I'm using LIKE because I'm making a "search" field, so the result should be a list of all users who's name is like the entered string. — Bv202, Mar 13 '11 at 19:19
@Bv202 - Not sure if there is a "most portable" string concatenation method. Will be interested myself if any of the answers cover that. — Martin Smith, Mar 13 '11 at 19:22
Do you want WHERE user_firstname LIKE(?) AND user_lastname LIKE(?)? — DOK, Mar 13 '11 at 19:23
Please be aware that not everyone uses their first name and last name, and some people don't even have them. Some people just have a single name. Some have names where the first name is the family name, not the last name. Some people have three names and their last one is not normally used. — Mark Byers, Mar 13 '11 at 19:33
For reference on naming conventions: [personal names](http://en.wikipedia.org/wiki/Personal_name). Since 'first_name last_name' describes the order but not function of names, it's not as bad as a 'given_name surname' scheme. Having "fullname" and "shortname" (there must be a better term for a name someone is addressed by) columns is better, as it is more easily encompasses different naming conventions. — outis, Mar 13 '11 at 19:50

score 23 · Accepted Answer · edited May 23 '17 at 12:30

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.

Oh well, way too easy... somehow I must have missed the CONCAT keyword... Thanks for the link regarding SELECT *; that's interesting :) — Bv202, Mar 13 '11 at 19:21
@Bv202: Not at all. `COUNT(*)` is a special case. The columns themselves aren't examined for the tally (though they will be for any other clauses), just the matching rows. Even without this optimization, changing a table definition won't affect `COUNT(*)` in ways that would cause problems. — outis, Mar 13 '11 at 19:36

score 2 · Answer 2 · edited Jun 09 '15 at 06:37

2

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.

edited Jun 09 '15 at 06:37

Jaskaran Singh

2,392
24
39

answered Mar 13 '11 at 19:17

Mark Byers

811,555
193
1,581
1,452

score 1 · Answer 3 · answered Mar 13 '11 at 19:15

1

SELECT * 
FROM   sam_users 
WHERE  TRIM(Concat(user_firstname, ' ', user_lastname)) LIKE ?;

answered Mar 13 '11 at 19:15

Pentium10

204,586
122
423
502

Combining two fields in SELECT statement

3 Answers3