Results 1 to 3 of 3

Thread: Ltrim + Rtrim

  1. #1
    Join Date
    Apr 2004

    Unanswered: 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 ?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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 to remove whatever characters you find offensive, according to your processing rules.


  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts