Clearing line-breaks in SQL Server text fields

I was recently doing a data export for a client that included a bunch of ostensibly plain-text data fields that include any number of tabs, carriage returns and line feeds that were mucking up the csv file. And oh, how I fought that data to make it look nice. It was many, many hours past when it should have occurred to me that I finally thought to clean up the fields on export, NOT during import. Such is the problem with tunnel visions.

Anyway, for future reference, and easy way to clean up fields to simply replace the offending characters, using something along the following lines:


SELECT replace([field],char(10),'[replacementchar]') FROM [table]

So, I wanted to replace line feeds (char(10)) and carriage returns (char(13)), so I doubled that to:


SELECT replace(replace([field],char(10),' '),char(13),' ') FROM [table]

And it all worked beautifully. I’m writing & storing it here on the hopes if I run into this again, it’ll be googleable.

%d bloggers like this: