Abdul Manaf Abdul Manaf - 2 months ago 8
MySQL Question

Make spaces consistent

I need to write a query which will make spaces between the string consistent

i.e. Field order_text contains strings they may be separated by single space or double or triple we are not sure about how many spaces are there. What I need to do is make these spaces consistent (i.e. 2 spaces)

mysql> SELECT order_text FROM customer_cp_abdul LIMIT 4;
+------------------------------------------+
| order_text |
+------------------------------------------+
| Mar-2009-1008 |
| Mar-2009-514 Sep-2009-1603 Mar-2010-22 |
| Mar-2009-2505 May-2009-2733 |
| Mar-2009-1160 |
+------------------------------------------+
4 rows in set (0.00 sec)

Answer

Here is a trick you can use for this:

select replace(replace(replace(order_text, ' ', '<>'), '><', ''), '<>', ' ')
from customer_cp_abdul
limit 4;

It replaces spaces with "<>". Multiple spaces end up looking like '<><><>'. Then, the >< are removed, so you get '<>'. And then one more replace. Voila! A single space.