SQL Server Performance

[Resolved] Suppress CRLF in a field

Discussion in 'SQL Server 2005 General Developer Questions' started by Phthisis, Jan 25, 2007.

  1. Phthisis New Member

    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 you
  2. Adriaan New Member

    SELECT REPLACE(MyColumn, CHAR(13) + CHAR(10), '')
    FROM MyTable
  3. Phthisis New Member

    so this code specifically removes CRLF from any position in a field?
  4. FrankKalis Moderator

    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
    Microsoft SQL Server MVP
  5. Phthisis New Member

    the fields in question are varchar fields, so i will give this a go and see how it works out.

    Thanks all!
  6. Adriaan New Member

    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), ' ')
  7. Phthisis New Member

    tried it out, worked like a charm.

    Thanks again!

Share This Page