Combining Multiple Columns' Data

[Percipient]

In some of my tables I have two columns for names; one for the last name and one for the first. I did this mostly so the names could be sorted by last name, then first name. But when the names are displayed they're usually put together as first_name last_name. Is it possible in a SELECT query to retrieve the information in both these columns, with a space between, as a single result, instead of having to put them together afterwards with PHP? As a rough and non-functional example...

SELECT first_name AND " " AND last_name AS name

I'm pretty certain that doesn't work, as I've tried it.

Thanks for the help.

[freakysid]

String concatenation is what you are after. Most langauges including MySQL have functions to do this. Here are two functions in MySQL. Note that with CONCAT if a field has a null value the function will return null. Whereas in CONCAT_WS null values are simply ignored (probably more helpful).

From Chapter 7.4.10 of the manual:

CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form:

mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'

CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULLs and empty strings, after the separator argument. The separator will be added between the strings to be concatenated:

mysql> select CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> select CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'

http://www.mysql.com/doc/S/t/String_functions.html

So an example for your case would be:

SELECT CONCAT_WS(' ', first_name, last_name) AS name ...

[Percipient]

That is, indead, exactly what I was looking for. Thanks much, Sid.
The trouble was, like a lot of other technical things, you have to know the proper term to look it up in the index, before you can find anything. In this case, I wasn't familiar with concatenations, although I'd seen it used in something a time or two before. Now I know.

[nguip]

Hmm...

This is an interesting thing to know.. it's useful

Thanks freakysid