SQL Server Performance Forum – Threads Archive
[Resolved] Suppress CRLF in a field
Hello all, I am doing a simple select on a number of fields then exporting to text files. There is one field that randomly has CRLF’s throughout it and it messes up my text file because it reads those CRLF’s and puts my data out of alignment. I was wondering if there is some kind of command or function i can use in my select statement to purge CRLF’s from a field? Thank youSELECT REPLACE(MyColumn, CHAR(13) + CHAR(10), ”)
FROM MyTable
so this code specifically removes CRLF from any position in a field?
It will remove all occurrances of the specified pattern in a (VAR)CHAR column. If you’re talking about a TEXT column, things are difference and then it might be better to use some client app to do this work. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
the fields in question are varchar fields, so i will give this a go and see how it works out. Thanks all!
By the way, in case you want to replace the CRLF with a space instead of a zero-length string: REPLACE(MyColumn, CHAR(13) + CHAR(10), ‘ ‘) Sometimes CRs and LFs can be messed up, in which case it would be safer to nest a few REPLACE calls – note that this may well slow down your query, compared to the single REPLACE call: REPLACE(REPLACE(REPLACE(REPLACE(MyColumn, CHAR(13) + CHAR(10), ‘ ‘), CHAR(10) + CHAR(13), ‘ ‘), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘)
tried it out, worked like a charm. Thanks again!
]]>