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
)