Remove multiple space in between string to a single space-CodePudding

Trying to clean up string data. TRIM removed leading and trailing spaces. Using REPLACE as REPLACE (col_name," ","") removed all spaces. Need a solution that will result in expected output.
Sample data:
7136 South Yale #300 Tulsa,;Oklahoma
428 NW 10th St. OKC
2903 W. Britton Road OKC

Expected output : 7136 South Yale #300 Tulsa,;Oklahoma
428 NW 10th St. OKC
2903 W. Britton Road OKC

I use MySQL 5.7.

CodePudding user response：

On MySQL 8 we could just have done a regex replacement on \s{2,} and replaced with a single space. On 5.7 this is a bit harder. Assuming that each address would only have at most one block of two or more unwanted spaces in it, we use substring operations here:

SELECT address,
       CASE WHEN address LIKE '%  %'
            THEN CONCAT(SUBSTRING(address, 1, INSTR(address, '  ') - 1), ' ',
                        LTRIM(SUBSTRING(address, INSTR(address, '  '))))
            ELSE address END AS output
FROM yourTable;

Demo

The above logic uses an INSTR() trick to find the start of the block of two or more spaces. It generates the output address by piecing together the two substrings on either side of this block, with excess spaces removed.