1 step regexp_replace instead of two steps-CodePudding

select replace(REGEXP_REPLACE(lower('new york, ,ny usa '),r'[ ] ', ' '),', ,',',') c2

returns 'new york,ny usa '

How can I do this in one step. ie remove all the white space between , , and replace the result ,, with ,

CodePudding user response：

You can use;

([^,] ,)[, ]

View Demo

([^,] ,) - Capture group which is returned with \1 Match anything up to the next occurance of a comma and also capture the comma.

[, ] - Exclude spaces and commas. The match fails at the next occurance of [[:alpha:]] characters

CodePudding user response：

You could use REGEXP_REPLACE with capture groups:

SELECT REGEXP_REPLACE('new york,      ,ny usa ', r'([^,] )[, ] (\w ) (\w )', r'\1,\2 \3');

Here is a regex demo showing that the pattern and replacement logic are working.