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.
I fully agree with this idea! I think it is good to clear them sometimes
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!
Great post. This solved a similar problem I had! Thank you.
thank you for the great post.
So glad I found this post. Thanks for sharing it.
simple and elegant ,thanks a lot for posting this.
perfect!
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.
Try converting the field to a varchar(max) first, then run the query.
Thanks a tonne, Steven
Your post has helped me keep a lot of hair on my head.
Perfect !!!! Thank you so much