[thelist] mysql and CONCAT...

[Jason Handby]

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