How to remove CrLf from strings in sql

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  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]

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  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".

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  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

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  December 21, 2010 at 1:35 pm

  #1265264

  Thanks Scott/Jason

  Works fine........

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

  Thanks [/font]

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  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