Replace space (" ") with no space ("") in one column

  • I have a table like this:

    ID | Propinsi | Kota |
    _________________________
     1 | Aceh     | Denpasar
     2 | Aceh     | Banda Aceh
     3 | Sumatera | Asahan
    

    This table has many rows. The problem is I want to replace the space before the text in column Kota for all rows like this:

    ID | Propinsi | Kota |
    _________________________
     1 | Aceh     |Denpasar
     2 | Aceh     |Banda Aceh
     3 | Sumatera |Asahan
    

    I searched Google, the function replace in MySQL only affects one row:

    SELECT REPLACE(string_column, 'search', 'replace') as Kota
    

    Can someone fix my problem?

  • Joe Taras

    Joe Taras Correct answer

    7 years ago

    Try this:

    To show without space:

    select trim(kota) from yourtable
    

    To change your data:

    update yourtable set kota = trim(kota);
    

    TRIM function is different to REPLACE. REPLACE substitutes all occurrences of a string; TRIM removes only the spaces at the start and end of your string.

    If you want to remove only from the start you can use LTRIM instead. For the end only you can use RTRIM.

  • Run New Query in mysql

    select REPLACE(kota,' ','') from table-name
    

    this will show the result how it looks like after trimming spaces from the column and to update

    update table-name set kota = REPLACE(kota,' ','')
    

    Save.

    The asker only wants to "Replace the Space berfore text on column Kota for All ROw". Your solution will replace all spaces, not just the leading ones.

  •         /*in Sql remove extra space b/w string*/
    SELECT REPLACE(@Variable, ' ', '')
    
            OR 
    
              /*in Sql remove extra space b/w string*/
            DECLARE @str varchar(150)
            SET @str='Hello    Welcome   to   stackover     flow '
            Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')
    
    
    
        /*in c#*/
         // Mysample string
                    string str ="hi you           are          a demo";
    
                    //Split the words based on white sapce
                    var demo= str .Split(' ').Where(s => !string.IsNullOrWhiteSpace(s));
    
                    //Join the values back and add a single space in between
                            str = string.Join(" ", demo);
    
        //output: string str ="hi you are a demo";
    

    Please explain how your query solves the author's problem; answers without explanation generally aren't received well.

  • UPDATE yourtable SET kota = TRIM(BOTH ' ' from kota);
    

    How is this different from the accepted answer?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM