Remove all white space in a table

Discussion in 'Databases' started by Astroman, Dec 30, 2008.

0
  1. #1
    Is there a way to remove all white space, left and right, in one go for every field in a single table?

    I've just copied and pasted a load of sql entries in and they're all a bit messy with spaces left and right, but otherwise fine. If you go to edit one and do nothing then save it the entry is straightened just fine, but I have like 10000 rows so I hoped there was a quicker way.
     
    Astroman, Dec 30, 2008 IP
  2. jacobbannier

    jacobbannier Active Member

    Messages:
    1,155
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    90
    #2
    I don't get what your asking, maby post a screenshot to show what it looks like now
     
    jacobbannier, Dec 30, 2008 IP
  3. phper

    phper Active Member

    Messages:
    247
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #3
    Try this:
    UPDATE tableName SET field1 = TRIM(field1), field2 = TRIM(field2), field3 = TRIM(field3);

    You have to specify each field name but at least it will be done for all rows.
     
    phper, Dec 30, 2008 IP
    Astroman likes this.
  4. Astroman

    Astroman Well-Known Member

    Messages:
    2,355
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #4
    Thanks phper, I know how to do that - just wondered if there was a one-fell-swoop command for the whole table. :)

    Edit: I just discovered this code to remove tabs from table vos_accounts and field email, which is what my white spaces are it seems: UPDATE vos_accounts SET email=TRIM("\t" FROM email)

    That works great, but a command for the whole table would be cool.
     
    Astroman, Dec 30, 2008 IP
  5. phper

    phper Active Member

    Messages:
    247
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #5
    The query that you found is actually basically the same as the one I recommended you to use, except that yours only trims tabs rather than 'any' whitespace. And they do update the whole table, unless if you add a 'WHERE' condition to the query of course.
     
    phper, Jan 1, 2009 IP
  6. Astroman

    Astroman Well-Known Member

    Messages:
    2,355
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #6
    Yeah, it is basically the same, that's why I gave you a plus rep for putting me onto it. :)

    Sometimes though I think if you have tabs in there you can't get rid of them the normal way, well doing the tab thing worked for me anyway. It doesn't update the whole table though, just the column (field) you specify. It didn't matter in the end really, I only had 3 columns to edit.
     
    Astroman, Jan 1, 2009 IP