SQL Select - Combine number fields to a string and handle NULLs 1

[Baesucks]

I have 4 numeric data fields that I want to display as a single field using a text delimiter (like "/") between the values.

So if...
Field1=5
Field2=8
Field3=2
Field4=1

My SQL would produce a single field result of (text) "5/8/2/1".

I have it working using CAST statements and concatinating those with '/' text strings, HOWEVER... when one of the numeric fields is NULL it displays it in my text string result as a ZERO.

So if
Field1=5
Field2=NULL
Field3=2
Field4=1

I want a result field of "5/ /2/1"
(a blank where the null value is)
instead I'm getting "5/0/2/1"

Here is the code I'm currently using for selecting this combined field.

CAST(ISNULL(Field1, ' ') AS varchar(4)) + '/' + CAST(ISNULL(Field2, ' ') AS varchar(4)) + '/' + CAST(ISNULL(Field3, ' ') AS varchar(4)) + '/' + CAST(ISNULL(Field4, ' ') AS varchar(4)) As Remaining,

Any suggestions?

 

[gmmastros]

Cast first and then use IsNull. Ex:

IsNull(Cast(Field2 As VarChar(4)), ' ')

IsNull will return a data type that matches the argument. In this case, it's an integer argument, so IsNull returns an integer. It's odd, but a space will convert to the value 0. Ex:

[tt][blue]Select Convert(Int, ' ')[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom