I have a long query that I'd like to replace the , with a carriage return to put all the fields on their own line but I can't figure out how to do it in the editor. It wont be fool proof, but better than doing it by hand.
How do I search for a ', ' and replace with ', '<CR><LF> in SQL Server Management Studio's editor
Viewed 32414
5 Answers
In SQL Server Management Studio (SSMS), use the find/replace box with the "Use Regular Expressions" box selected and replace , (comma character) with ,\r\n (comma carriage return line feed.)
The \r represents the escaped carriage return CHAR(13) character and the \n represents the escaped linefeed CHAR(10) character.
If you're doing the opposite of removing/replacing CR/LF characters you'll need to consider your file format. Some file types use only CR, others use only LF, and most Windows formats use CR + LF. So you may need to play with the replace of \r\n with just \r or just \n for those different formats.
In management studio 2017 there is a button with a little gear and square, if you hover over it it reads "Use Regular Expression". Select this and enter , for the find and ,\n for the replace.
Related