List:General Discussion« Previous MessageNext Message »
From:wim.delvaux Date:May 14 2008 4:04pm
Subject:Re: CONCAT doesn't work with NULL?
View as plain text  
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 <afan@stripped> 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/mysql?unsub=1


Thread
CONCAT doesn't work with NULL?Afan Pasalic14 May
  • Re: CONCAT doesn't work with NULL?Olexandr Melnyk14 May
    • Re: CONCAT doesn't work with NULL?wim.delvaux14 May
  • Re: CONCAT doesn't work with NULL?ewen fortune14 May
    • Re: CONCAT doesn't work with NULL?Afan Pasalic14 May
    • Re: CONCAT doesn't work with NULL?Afan Pasalic14 May
      • Re: CONCAT doesn't work with NULL?wim.delvaux14 May
  • RE: CONCAT doesn't work with NULL?Randall Price14 May
    • Re: CONCAT doesn't work with NULL?Afan Pasalic14 May
    • Re: CONCAT doesn't work with NULL?Afan Pasalic21 May
    • relay-bin.000157' not found Charles Brown7 Aug
      • Re: relay-bin.000157' not foundMoon's Father12 Aug
  • Re: CONCAT doesn't work with NULL?Paul DuBois14 May
  • Re: CONCAT doesn't work with NULL?王旭15 May