SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Vancouver, B.C., Canada
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Triming leading and trailing spaces of a all values in a table column?

    Hi,

    Is there an sql statement that will allow me to trim all leading and trailing spaces of all values in a table column?

    Cheers,

    Iain

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE table set colname = TRIM(colname);

  3. #3
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What database are you using?

    Cheers,
    D.

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Vancouver, B.C., Canada
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using mysql 5.

    Seems... UPDATE table set colname = TRIM(colname);

    Isn't the right syntax. I'm doing the sql statement in phpmyadmin.

  5. #5
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ken Masters View Post
    I'm using mysql 5.
    Then why didn't you post this question in the MySQL section?

    Mods, can we have this moved?

    Cheers,
    D.

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is the correct syntax for MySQL. What error did it give you?
    Sam Hastings

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Update tablename set colname = TRIM(colname);

    is the right syntax.

    colname is the name of the column whose values you wish to remove leading and trailing spaces from and tablename is the name of the table in which the column resides.

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Vancouver, B.C., Canada
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Philip, I must have written it incorrectly but it works now... although no rows were affected, which is strange because I can see leading spaces in the majority of my rows in that column. I'm not sure how to get rid of them besides manually removing them.

  9. #9
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a similar problem in the past - read the thread I posted for the solution
    Sam Hastings

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use the following

    SELECT * from tablename where colname like ' %' or colname like '% ';

    to see if any columns with leading or trailing spaces are reported.

    I am wondering if the character is not a space but is being seen as a space.


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
  •