How to remove CrLf from strings in sql

  • 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]

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • Thanks Scott/Jason

    Works fine........

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

    Thanks [/font]

  • 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