MySQL : how to remove double or more spaces from a string?

by

Here’s an old trick that does not require regular expressions or complicated functions.

You can use the replace function 3 times to handle any number of spaces, like so:

REPLACE('This is    my   long    string',' ','<>')

becomes:

This<>is<><><><>my<><><>long<><><><>string

Then you replace all occurrences of ‘><‘ with an empty string ” by wrapping it in another replace:

REPLACE(
  REPLACE('This is    my   long    string',' ','<>'),
    '><',''
)

This<>is<>my<>long<>string

Then finally one last replace converts the ‘<>’ back to a single space

REPLACE(
  REPLACE(
    REPLACE('This is    my   long    string',
      ' ','<>'),
    '><',''),
  '<>',' ')

This is my long string

This example was created in MYSQL (put a SELECT in front) but works in many languages.

Note that you only ever need the 3 replace functions to handle any number of characters to be replaced.

More Related Contents:

  1. How to convert a string to date in MySQL?
  2. How to find similar results and sort by similarity?
  3. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  4. How do you extract a numerical value from a string in a MySQL query?
  5. SQL order string as number
  6. How to remove all non-alpha numeric characters from a string in MySQL?
  7. How to escape single quotes in MySQL
  8. MySQL – length() vs char_length()
  9. Capitalize first letter. MySQL
  10. Can MySQL replace multiple characters?
  11. How to fix double-encoded UTF8 characters (in an utf-8 table)
  12. MySql – Way to update portion of a string?
  13. mysql: why comparing a ‘string’ to 0 gives true?
  14. Strip last two characters of a column in MySQL
  15. MySql Query Replace NULL with Empty String in Select
  16. Last index of a given substring in MySQL
  17. how to delete data from table A besides the customer service and director data in table B?
  18. Get records with max value for each group of grouped SQL results
  19. MySQL: multiple tables or one table with many columns? [closed]
  20. Add 2 hours to current time in MySQL?
  21. How to prepend a string to a column value in MySQL?
  22. How to get first day of every corresponding month in mysql?
  23. How can I change MariaDB to MySQL in XAMPP?
  24. MySQL: LIMIT by a percentage of the amount of records?
  25. Dynamic table name at sql statement
  26. MySQL Inner Join Query Multiple Tables
  27. MySQL 1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
  28. MySQL LEFT JOIN 3 tables
  29. Doctrine 2 mysql FIELD function in order by
  30. MySQL SELECT most frequent by group

Leave a Comment