SQL – How to convert line separated text to comma separated

The following T-SQL for Microsoft  SQL Server shows how to  convert line separated text to comma separated text.

For example, if we had a table with the following data

Lucky_Numbers
2
33
25
1
3
11
9

You could use the following SQL to convert the rows to a comma separated list.

WARNING: This command uses UPDATE to make changes to the database. Use with caution and ALWAYS backup before doing bulk changes.

UPDATE MyTableName
SET Lucky_Numbers = REPLACE( REPLACE( REPLACE( Lucky_Numbers, CHAR(13), ',' ), CHAR(10), ',' ), ',,', ',' )

This will update the table to:

Lucky_Numbers
2,33,25,1
3,11,9

How does this work?

Multiple nested REPLACE functions are being used to find and replace any “new line” characters with a comma. 

CHAR(10) and CHAR(13) can both be used for new lines – so to be safe we’re looking for both.

Finally, to clean up any empty values we’re looking for any double commas and replacing with a single comma.