Select statement with a NULL

[Bruce A. Julseth]

I have forgotten how to do this (At least I think I knew how)

I have SELECT CONCAT(A, ' ', B) as C ........

If A or B is NULL this select fails. What do I need to do so it won't
fail...

Thanks...

[Bruce A. Julseth]

"Bruce A. Julseth" <br*************@bellsouth.netwrote in message
news:f1**********@aioe.org...

>I have forgotten how to do this (At least I think I knew how)

I have SELECT CONCAT(A, ' ', B) as C ........

If A or B is NULL this select fails. What do I need to do so it won't
fail...

Thanks...

Never Mind. I found it on the internet...

SELECT CONCAT_WS('', A, ' ', B) as C .....

Thanks....

[Toby A Inkster]

Bruce A. Julseth wrote:

SELECT CONCAT_WS('', A, ' ', B) as C .....

That looks like some MySQL-specific voodoo. The standards-compliant way of
doing this is:

SELECT COALESCE(a,'')||' '||COALESCE(b,'') AS c

However, neither MySQL nor Microsoft SQL server support the concatenation
operator (||) correctly. Thus if you want to write code which is broadly
compatible on a variety of databases, one is forced to simply:

SELECT a, b

and then concatenate them in PHP itself:

if (strlen($row['a']) * strlen($row['b']))
$row['c'] = "{$row['a']} {$row['b']}";
else
$row['c'] = $row['a'].$row['b'];

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!