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',

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,

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,

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

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',

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

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, ''), ' ',

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

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

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

Re: CONCAT doesn't work with NULL?

- 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