SEO

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

Hi Afan,
You can use concat_ws

http://dev.mysql.com/doc/refman/5.0/...tion_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 <afan@afan.net> 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=e...tune@gmail.com
>
>

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 <afan@afan.net> 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=omelnyk@gmail.com
>
>


--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

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@afan.net> 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=omelnyk@gmail.com

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@afan.net]
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/mysql?unsub=Randall.Price@vt.edu

Thanks Ewen,
that's what I was looking for!



-afan

ewen fortune wrote:
> Hi Afan,
> You can use concat_ws
>
> http://dev.mysql.com/doc/refman/5.0/...tion_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 <afan@afan.net> 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=e...tune@gmail.com
>>
>>

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@afan.net]
> 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
>

actually, this will not work for me (or I got it wrong ) 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.





ewen fortune wrote:
> Hi Afan,
> You can use concat_ws
>
> http://dev.mysql.com/doc/refman/5.0/...tion_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 <afan@afan.net> 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=e...tune@gmail.com
>>
>>

On Wednesday 14 May 2008 18:52:20 Afan Pasalic wrote:
> actually, this will not work for me (or I got it wrong ) 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'

>
>
>
> ewen fortune wrote:
> > Hi Afan,
> > You can use concat_ws
> >
> > http://dev.mysql.com/doc/refman/5.0/...l#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 <afan@afan.net> 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=e...tune@gmail.com

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/...unction_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