Re: CONCAT doesn't work with NULL?
Price, Randall wrote: Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 this is good. though, if r.title is NULL I'll get an extra empty row on screen: john doe doe, inc. -- no title, empty row 123 main st. testtown, TE 12345 also, I would like to hear opinion about the following query: SELECT o.col1, o.col2, o.col3, ( SELECT CONCAT_WS('', r.first_name, ' ', r.last_name, '\n', r.organization, '', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email, '\nHome: ', left(r.phone_home, 3), '-', mid(r.phone_home, 3, 3), '-', right(r.phone_home, 4), '\nWork: ', left(r.phone_work, 3), '-', mid(r.phone_work, 3, 3), '-', right(r.phone_work, 4)) FROM registrants r, addresses a WHERE r.reg_id=o.registered_id and a.reg_id=r.reg_id and a.address_type='Business' LIMIT 1 ) as REGISTERED_BY, pm.payment_method as payment_method_name, f.form_name FORM_NAME FROM orders o, payment_methods pm, forms f WHERE o.order_id=.$order_id. AND pm.pm_id=o.payment_method AND f.form_id=.$form_id. is it smart/good way to have subquery this way or solution below is better one: SELECT o.col1, o.col2, o.col3, r.first_name, r.last_name, r.organization, r.title, a.address1, a.city, a.state, a.zip, r.email, r.phone_home, r.phone_work, pm.payment_method as payment_method_name, f.form_name FORM_NAME FROM orders o, payment_methods pm, forms f WHERE o.order_id=.$order_id. AND pm.pm_id=o.payment_method AND f.form_id=.$form_id. AND r.reg_id=o.registered_id AND a.reg_id=r.reg_id AND a.address_type='Business' in this case I have to create string REGISTERED_BY by php. reason I did it as example 1 is because I read in few books and online people saying do whatever you can using query in mysql rather then using php. it's faster, better, more secure,... thoughts? -afan Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) On 5/14/08, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: CONCAT doesn't work with NULL?
Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:02:42 Olexandr Melnyk wrote: It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) or CONCAT_WS IIRC W On 5/14/08, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CONCAT doesn't work with NULL?
Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
Thanks Ewen, that's what I was looking for! :D -afan ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
First, I want to thank to everybody on such afast respond. Thank you. Second, what would be difference between concat_ws and the Randalll's solution (bellow)? -afan Price, Randall wrote: Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:52:20 Afan Pasalic wrote: actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. Use a 'space' as sparator instead of '\n' :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_con cat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
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]
Re: CONCAT doesn't work with NULL?
i execute follow sql. select concat_ws('','d','\n','c'); the result is : ++ | concat_ws('','d','\n','c') | ++ | d c| ++ There are no result as you said. - Original Message - From: Afan Pasalic [EMAIL PROTECTED] To: ewen fortune [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, May 15, 2008 12:52 AM Subject: Re: CONCAT doesn't work with NULL? actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] 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? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]