Converting Varchar with null 1

[JLizOB]

I am trying to concatenate multiple fields into one field in a query. The problem I am having is that if any single one of these fields has a null alue the resulting value is null regardless if the other fields have values or not. I tried both the convert and cast functions but to no avail. Are If statements the only way around this?

 

[JLizOB]

Uggh! I'm sorry this was meant for the SQL Server forum, can I delete this or move it without cross posting?? Thanks! and sorry for the carelessness

 

[AlexCuse]

It'll get cleaned up somehow.

Have a look at SQL Server's COALESCE function though

Hope it helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

Here's a quick example:

[COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (n [COLOR=blue]varchar[/color](15), v [COLOR=blue]varchar[/color](15))

[COLOR=blue]insert[/color] @t 
[COLOR=blue]select[/color] [COLOR=red]'Alex'[/color], null
union all [COLOR=blue]select[/color] null, [COLOR=red]'JLizOb'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Coalesce'[/color], [COLOR=red]'Works?'[/color]

[COLOR=blue]select[/color] *, n + v [COLOR=blue]as[/color] THISISNULL, [COLOR=#FF00FF]coalesce[/color](n, [COLOR=red]''[/color]) + [COLOR=#FF00FF]coalesce[/color](v, [COLOR=red]''[/color]) [COLOR=blue]as[/color] THISWORKS
[COLOR=blue]from[/color] @t

Hope it helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[JLizOB]

Thanks Alex!