ltrim and rtirm is not working

Hi i have a select statement as

select empnum, len(empnum), ltrim(rtrim(empnum)), len(ltrim(rtrim(empnum))) from employee

When i execute this stament i get the following

1234 6 1234 6
4321 8 4321 8
1111 6 1111 6
2222 6 2222 6

How does this happens. Why ltrim and rtrim is not working here.what are you using to display these results, query analyser? enterprise manager? something else?

the length of 1234 is clearly not 6|||What does this yield:

select 'X' + ltrim(rtrim(empnum)) + 'X' from employee|||Ive got it, the input statement is from vb.net source, and the programmers have stored vbcrlf after each string. char(13) stored at the end and that is the reason y ltrim and rtrim is not working.

I removed the char(13) at the end and now it is working.

Thanks for your time

Ltrim + Rtrim

How do i remove carriage returns in SQL Server ? each of the lines have a carriage return as well as in front and back of the text.

Keith Waltin

Transport Ticketing Authority

03 9651 9066

I've tried the

update test.dbo.test
set bodytext1 = ltrim(rtrim(bodytext1))

but the whitespace/carriage returns still exists in the back and front of the text ? Anyone got any ideas ?The Ltrim() and RTrim() functions work on space characters (ASCII 32, Hex 0x20) only. They don't have any effect on other whitespace.

I'd write a UDF (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp) to remove whatever characters you find offensive, according to your processing rules.

-PatP|||declare @.cr char(1), @.lf char(1), @.crlf char(2), @.space char(1)
select @.cr = char(13), @.lf = char(10), @.space = char(32)
update test.dbo.test
set bodytext1 = replace(replace(replace(bodytext1, @.cr, ''), @.lf, ''), @.space, '')
where (
charindex(@.cr, bodytext1) > 0 or
charindex(@.lf, bodytext1) > 0 or
charindex(@.space, bodytext1) > 0
)