From: Afan Pasalic
Date: May 14 2008 4:11pm
Subject: Re: CONCAT doesn't work with NULL?
List-Archive: http://lists.mysql.com/mysql/212808
Message-Id: <482B0F47.1060905@afan.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:afan@stripped] 
> Sent: Wednesday, May 14, 2008 11:53 AM
> To: mysql@stripped
> 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
>