Post reply

How to remove CrLf from strings in sql

  • December 21, 2010 at 11:12 am

    #228993

    Hi I have to remove a Line feed charater from my varchar column and I was trying to use LTRIM/RTRIM but still the max len is showing 6 and not 4..

    Here is the full code...

    create table #test

    (

    id int identity,

    name varchar(15)

    )

    insert into #test (name)

    select 'abc '

    union all

    select 'abcd

    '

    union all

    select 'def'

    union all

    select 'xyz'

    select * from #test

    select top 1 id,MAX(len(ltrim(rtrim(name)))) from #test

    group by id

    order by 2 desc

    output is coming 6, it should be 4

    Any help on this...

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • December 21, 2010 at 12:12 pm

    #1265224

    Try something like:

    REPLACE(REPLACE(column, CHAR(13), ''), CHAR(10), '')

    Of course, CHAR(13) = cr, CHAR(10) = lf

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 21, 2010 at 12:21 pm

    #1265228

    To make sure various combinations are replaced, I have used something like this:

    REPLACE(REPLACE(REPLACE(REPLACE(dbo.yourcolumn, CHAR(13) + CHAR(10), ' ... '),

    CHAR(10) + CHAR(13), ' ... '), CHAR(13), ' '), CHAR(10), ' ... ')

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2010 at 1:35 pm

    #1265264

    Thanks Scott/Jason

    Works fine........

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • December 21, 2010 at 1:44 pm

    #1265268

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply