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.

11 Replies to “Clearing line-breaks in SQL Server text fields”

  1. I used this same method to remove line feeds for data outputted to a CSV (pipe delimited)  file. I have a script that uses sqlcmd and it works well. I did have a problem when I needed to nest the replace command to remove the pipe character “|” which was causing the client problems when importing. 

    When nesting instead of replacing with a space by typing in ‘ ‘ I needed to enter CHAR(32) instead, otherwise my query would fail. I had to do something like this: replace(replace([Field], CHAR(10), char(32)), char(124), char(32)). 

    Thanks for the post!

  2. Hi,

    i have the coloum data type as “ntext” when i tried to perform that action by using above quert it is throwing error. please suggest how to get out of this issue. i have lot of data to be exported toexcel sheet but due to line breaks data is not in proper format.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: