December 21, 2010 at 11:12 am
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 [/font]
December 21, 2010 at 12:12 pm
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
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
Thanks Scott/Jason
Works fine........
Thanks [/font]
December 21, 2010 at 1:44 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy