: 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
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
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,
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,
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
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
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
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
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
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
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
-
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
12 matches
Mail list logo
