mysql concat does not like NULL values – use concat_ws instead

This just caused me several moments of pain. Here’s hoping I can remove that for you.

I was needing to update a VARCHAR column by incrementally adding values read from another source. Simplest SQL (I thought) was:

UPDATE table SET field = CONCAT(field, 'value ') WHERE id = ID;

All ran fine. But pretty much all of the fields came back NULL. And to find out you have to actually read the details in the mysql manual. A snip from the official write-up on concat:

CONCAT() returns NULL if any argument is NULL.

There’s the problem. Most/all my starting values were NULL. So concatenating actual values in the field did nothing as the NULL won.

The solution was very simple. Replace the CONCAT() function with CONCAT_WS() where the “ws” bit means “with separator”. Rather than just adding a space after the value in my example, specify the delimiter as the first parameter. This makes the revised SQL command:

UPDATE table SET field = CONCAT_WS(' ', field, 'value ') WHERE id = ID;

It’s a lot more forgiven with NULLs; again snipped from the manual:

  • If the separator is NULL, the result is NULL
  • CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Now it does not matter if there are any NULL values involved. All just works.

4 thoughts on “mysql concat does not like NULL values – use concat_ws instead

  1. nexus

    hmm… that’s funny. can’t seem to get it to work for me at all. any ideas??

    | protocol_version | 10 |
    | version | 5.1.41-3ubuntu12.10 |
    | version_comment | (Ubuntu) |
    | version_compile_machine | i486 |
    | version_compile_os | debian-linux-gnu |

    mysql> update bookmarks set keyword = CONCAT_WS(‘ ‘,keyword,’recipe;’) where title like ‘%recipe;%’;
    Query OK, 0 rows affected (0.01 sec)
    Rows matched: 0 Changed: 0 Warnings: 0

    mysql> select id, title, keyword from bookmarks where title like ‘%recipe%’;
    | id | title | keyword |
    | 266 | The Indian Slow Co | NULL |
    | 319 | Fitness Recipes | Get Health F | fitness;gym;24_Hour_Fitness; |
    2 rows in set (0.00 sec)

  2. nexus

    forget it figured it out… :)
    had an ; hanging out in the wrong place.
    fixed it and it worked!! :D
    thank you for your lovely page, sure helped!!! :)


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>