Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to remove CrLf from strings in sql Expand / Collapse
Author
Message
Posted Tuesday, December 21, 2010 11:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 14, 2015 12:43 PM
Points: 340, Visits: 891
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...



---------------------------------------------------

Thanks
Post #1037870
Posted Tuesday, December 21, 2010 12:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 3,033, Visits: 4,641
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1037898
Posted Tuesday, December 21, 2010 12:21 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 19,175, Visits: 17,474
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
Post #1037902
Posted Tuesday, December 21, 2010 1:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 14, 2015 12:43 PM
Points: 340, Visits: 891
Thanks Scott/Jason
Works fine........



---------------------------------------------------

Thanks
Post #1037944
Posted Tuesday, December 21, 2010 1:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 19,175, Visits: 17,474
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
Post #1037951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse