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.