SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LTRIM() not removing spaces

    I've got a rather arduous data import/conversion task to complete with a huuuuge table in SQL Server 2005.

    There is a column called content, of which many values are preceded by one or more spaces. I tried running the following query to remove these values:

    Code sql:
    UPDATE documents SET content = LTRIM(content);

    It comes back with 9500 rows affected but the query has not affected a single row: there are still spaces at the start of many of them.

    I hope this describes the problem adequately. Is someone able to help me out here?

    Cheers

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how do you know they're spaces?

    after you run your LTRIM update, what does this query give you --
    Code:
    SELECT COUNT(*)
      FROM documents 
     WHERE content LIKE ' %'
    that's a space followed by the percent sign
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That query returns a big fat "0"

    Is there any way of finding out what that character is?

    Edit: ... and more importantly, how to remove it.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    try some of these --
    Code:
    SELECT COUNT(*)
      FROM documents 
     WHERE content LIKE CHAR(9)+'%' -- tab
    Code:
    SELECT COUNT(*)
      FROM documents 
     WHERE content LIKE CHAR(10)+'%' -- line feed
    Code:
    SELECT COUNT(*)
      FROM documents 
     WHERE content LIKE CHAR(13)+'%' -- carriage return
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Line feed returned 5500 odd rows.

    Should I use something like this to remove them?

    Code sql:
    UPDATE documents SET content = SUBSTRING(content, 2, LEN(CONTENT)) WHERE content LIKE CHAR(10)+'%';

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yeah, that should work, and of course you will back up your table before testing it, yeah?



    p.s. whaddya bet there's a CHAR(13) sitting right after the CHAR(10)s...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah go on then

    Thanks for your help!

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    p.s. whaddya bet there's a CHAR(13) sitting right after the CHAR(10)s...
    Indeed


Bookmarks

Posting Permissions

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