SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict Percipient's Avatar
    Join Date
    Jun 2000
    Location
    Summerland, BC, Canada.
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ed Rands

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ...

  3. #3
    SitePoint Addict Percipient's Avatar
    Join Date
    Jun 2000
    Location
    Summerland, BC, Canada.
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ed Rands

  4. #4
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...

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

    Thanks freakysid
    Ngu I.P.
    Web Developer


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •