how to concatenate in ms sql server 2005

I have a table with columns s1,s2,s3,s4,s5,s6. I want to concatenate and seperate the columns with a space and semi colon only when the value exist in the column.e.g. s1 has value v1, s2 value v2, s3 null, s4, v4, s5 null, s6 valud v6. The out put should be like

v1 ; v2;  v4; v6

thanks
mmalik15Asked:
Who is Participating?
 
NorieVBA ExpertCommented:
Try ISNULL, and include the semi-colon.

ISNULL([s1]+';', '')+ ISNULL([s2]+';', '')+ ISNULL([s3]+';', '')+ ISNULL([s4]+';', '')+ ISNULL([s5]+';', '')+ ISNULL([s6]+';', '')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
mmalik15Author Commented:
thanks for the comment but i don't how that example relates to my query. I have used query like this

select RecordID, Title, URL, [Publication Date], teaser, s1+'; '+s2+'; '+s3+'; '+s4+'; '+s5+'; '+s6 as msubjectgen from dbo.Duets

but the prob with the above is it also concatenates the null values which offcourse I don't want. Any further ideas
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
NorieVBA ExpertCommented:
Try COALESCE or ISNULL

For COALESCE:

COALESEC(s1, '')+'; '+COALESEC(s2, '')+'; '+COALESEC(s3, '')+'; '+COALESEC(s4, '')+'; '+COALESEC(s5, '')+'; '+COALESEC(s6, '')

For ISNULL just replace COALESCE.

Though I'm sure there are much better ways, perhaps.
0
 
mmalik15Author Commented:
coalesce is good but the problem i have is semicolon will be concatenated regardless if there is value for a column. I want to concatenate semicolon and space only when there is a value in the column
0
 
mmalik15Author Commented:
thanks for the comment again. I am still getting semicolons when there are null values.e.g.
Urinary incontinence;Urological and genital disorders;;;;;
0
 
NorieVBA ExpertCommented:
Are you sure the values are null?

It works for me with some simple data.
0
 
mmalik15Author Commented:
alright.

Sorry imnorie I have checked again and yes you're right. The column values are blank but they don't say as having null. How can we amend the above query for blank values?

0
 
ralmadaCommented:
try this

ISNULL(nullif([s1], '')+';', '')+ ISNULL(nullif([s2],'')+';', '')+ ISNULL(nullif([s3],'')+';', '')+ ISNULL(nullif([s4],'')+';', '')+ ISNULL(nullif([s5],'')+';', '')+ [s6]

or using rtrim if necessary

ISNULL(nullif([rtrim(s1)], '')+';', '')...
0
 
mmalik15Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.