Concatenating Row Values into 1 Record/String with Commas Suppressed

[StateGov]

Hi Everyone,

I have the following in a view that I created in SQL 2005:

RTRIM(County_Code_1 +', '
+ County_Code_2 +', '
+ County_Code_3+', '
+ County_Code_4 +', '
+ County_Code_5) As County,

While it serves the purpose of joining all of the fields into one single string, it does not suppress the commas if the fields are null and the output looks sort of like Hunterdon, Atlantic,,,. Any suggestions, please? I searched the forum and found many tips for concatenating, however, there was nothing to suppress the commas if the field is null. Thank you.

 

[bborissov]

ISNULL(NULLIF(County_Code_1,'')+', ','') +
ISNULL(NULLIF(County_Code_2,'')+', ','') +
ISNULL(NULLIF(County_Code_3,'')+', ','') +
ISNULL(NULLIF(County_Code_4,'')+', ','') +
ISNULL(NULLIF(County_Code_5,'')+', ','')) As County,

NOT TESTED!!!!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[StateGov]

Thank you.

 

[StateGov]

It still leaves a comma after populating with the data (ie. BUR, MID, MON, - Any other suggestions? Thanks.

 

[markros]

rtrim(coaleasce(County1+',','') + coalesce(County2+',','') + etc.)

 

[StateGov]

Hello,

It still does not suppress the commas:

PAS, , , , ,

When there are null values for the five additional county fields, it still adds the commas. I used exactly as follows:

rtrim(COALESCE(County_Code_1+',','') + COALESCE(County_Code_2+',','') + COALESCE(County_Code_3+',','') + COALESCE(County_Code_4+',','') + COALESCE(County_Code_5+',',''))As County,

Any suggestions? Thanks everyone.

 

[markros]

Are you sure the values are null and not blank?

 

[StateGov]

Hello,

I am still new to this so I apologize for my ignorance. Null would be if it actually had the word "Null" in the field? Blank is if nothing showed in the field at all? They are blank if the above scenario is true. Does this help?

Thank you.

 

[markros]

you may try then

select case when County1 IS NULL or len(ltrim(County1)) = 0 then '' else ltrim(rtrim(County1)) + ', ' end +
case when County2 IS NULL or len(ltrim(County2)) = 0 then '' else ltrim(rtrim(County2)) + ', ' end

etc.

May be it could be simplified, I don't have SQL Server at the moment to test.

 

[StateGov]

That worked with a little tiny tweak:

County =
case when Cty1 IS NULL or len(ltrim(Cty1)) = 0 then '' else ltrim(rtrim(Cty1)) + ', ' end +
case when dbo.Cty2 IS NULL or len(ltrim(.Cty2)) = 0 then '' else ltrim(rtrim(Cty2)) + ', ' end +
case when Cty3 IS NULL or len(ltrim(Cty3)) = 0 then '' else ltrim(rtrim(Cty3)) end,

Thank you for all of your help. I really, really, appreciated it.

 

[markros]

You're welcome, glad to help.