kbball kbball - 3 months ago 9
MySQL Question

Remove trailing spaces and add them as leading spaces

I would like to remove the trailing spaces from the expressions in my column and add them to beginning of the expression. So for instance, I currently have the expressions:

Sample_four_space
Sample_two_space
Sample_one_space


I would like to transform this column into:

Sample_four_space
Sample_two_space
Sample_one_space


I have tried this expression:

UPDATE My_Table
SET name = REPLACE(name,'% ',' %')


However, I would like a more robust query that would work for any length of trailing spaces. Can you help me develop a query that will remove all trailing spaces and add them to the beginning of the expression?

Answer

If you know all spaces are at the end (as in your example, then you can count them and put them at the beginning:

select concat(space(length(name) - length(replace(name, ' ', ''))),
              replace(name, ' ', '')
             )

Otherwise the better solution is:

select concat(space( length(name) - length(trim(trailing ' ' from name)) ),
              trim(trailing ' ' from name)
             )

Both these cases count the number of spaces (in or at the end of). The space() function then replicates the spaces and concat() puts them at the beginning.