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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
