SQL Query: data with comma

Question

Hi All,

Im using sql server 2005. i want data with comma. for example ['5000'],['5001'],..

but the last record should not include comma. Pls help me.

Query:

select '['''+convert(varchar,parcelid)+'''],' from sampletable

Answer 1

You can use regular expressions to remove the last comma or do it using your programming language (ASP etc. like a chop function or something).

http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx

Answer 2

Try the COALESCE function

SELECT @groupedText = COALESCE(@groupedText, '') + [Text] + ','
     FROM Requirement
     WHERE CampaignId = @campaignId
     ORDER BY [Text]

Then you could try one of the string functions to kill the end comma

T-SQL string functions

Answer 3

Consider using XML for this purpose. The "aggregate concatenation" solution may not be reliable, because it is not clearly documented and supported. You can get rid of the final comma with SUBSTRING, as boon suggested.

See this thread.