Removing an end space MySQL database

Discussion in 'IT & Internet' started by UKSBD, Jan 30, 2020.

  1. UKSBD

    UKSBD Not a real duck Staff Member

    9,544 1,799
    My directory has a table for the business listings and within that has a field for the category

    I initially allowed people to create their own categories, but this resulted in people sometimes leaving a space after the category.

    for example;
    I have one which is "Health "

    How do I correct this in the database?

    I've tried

    SET `catergory`='Health' WHERE `catergory`='Health ';

    but no joy.

    What's the easiest way of correcting this.
     
    Posted: Jan 30, 2020 By: UKSBD Member since: Dec 30, 2005
    #1
  2. UKSBD

    UKSBD Not a real duck Staff Member

    9,544 1,799
    After a bit of research and testing

    I've tried
    SET catergory = RTRIM(catergory) WHERE `catergory`='Health '

    does that look an OK way of doing it?

    If I left of the WHERE would it correct all the categories?

    Would it also work in a category where someone left 2 spaces ie. "Financial Services "?
     
    Posted: Jan 30, 2020 By: UKSBD Member since: Dec 30, 2005
    #2
  3. gpietersz

    gpietersz UKBF Enthusiast Full Member

    630 124
    RTRIM removes trailing spaces, and only trailing spaces, so would work with something like "financial services " (assuming you only want to remove the trailing space) https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_rtrim

    It looks right to me, and it should work right without the where, but I think its a good idea to always test on a copy of the DB before you do it on production.
     
    Posted: Jan 30, 2020 By: gpietersz Member since: Sep 10, 2019
    #3