On May 14, 2008, at 10:53 AM, Afan Pasalic wrote:
> hi,
>
> I have query
> SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
> '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
> a.zip, '\n', r.email)
> FROM registrants r, addresses a
> WHERE r.reg_id=121
>
> if any of columns has value (e.g. title) NULL, I'll get as result 0
> records.
> If query doesn't have concat() - it works fine.
>
> Why is that?
That's how CONCAT() is documented to work:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
"CONCAT() returns NULL if any argument is NULL."
You might want to try CONCAT_WS('', ...) instead. CONCAT_WS() isn't
fazed
by NULL values the same way that CONCAT() is. :-)
mysql> select CONCAT('a',NULL,'b');
+----------------------+
| CONCAT('a',NULL,'b') |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.07 sec)
mysql> select CONCAT_WS('','a',NULL,'b');
+----------------------------+
| CONCAT_WS('','a',NULL,'b') |
+----------------------------+
| ab |
+----------------------------+
1 row in set (0.00 sec)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
