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, September 12, 2014 11:45 AM
Points: 336, Visits: 867
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:36 PM
Points: 2,110, Visits: 3,173
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1037898
Posted Tuesday, December 21, 2010 12:21 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 17,655, Visits: 15,509
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
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, September 12, 2014 11:45 AM
Points: 336, Visits: 867
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 @ 6:43 PM
Points: 17,655, Visits: 15,509
You're welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1037951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse