[thelist] mysql and CONCAT...
Jason Handby
jason.handby at corestar.co.uk
Tue Aug 21 05:02:07 CDT 2007
Hi Tris,
> I've got 2 fields.
> Depending on the result, one might be NULL, this is intentional.
> However, if the 2nd IS null, I want to combine it with eh first...
> eg:
> field1 | field2
> sign up | NULL
> points claimed | NULL
> points spent | claimed prize X
>
> So in my output I want a new field to be created using CONCAT
>
> CONCAT(field1, " - ", field2) as field1
>
> so I WANT the output of field1 to be:
> sign up
> points claimed
> points spent - claimed prize X
>
> but I'm getting a blank field1 if field 2 is null...
>
> How can I get round that?
When field2 is NULL, your expression will evaluate to NULL.
You probably want to use COALESCE():
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#functio
n_coalesce
Something like this:
CONCAT(field1, " - ", COALESCE(field2, "")) as field1
Jason
More information about the thelist
mailing list