Use SQL to Replace Multiple Commas in a String with a Single Comma

Question

Input: Hi,,How are you? Fine, thanks ,, , ,,,, , James,Arden.

I would like to replace all consecutive commas with a single comma and a space.

The output should be:

Hi, How are you? Fine, thanks, James,Arden.

SELECT REGEXP_REPLACE('Hi,,How are you? Fine, thanks ,, ,  ,,,, , James,Arden.', ',,+', ', ') FROM DUAL;

I haven't tested it yet as I don't have access to the Oracle system yet.

Take a look: http://sqlfiddle.com/#!4/d41d8/28054 – PM 77-1 Apr 21 '14 at 01:24 — PM 77-1, Apr 21 '14 at 01:24

score 1 · Accepted Answer · answered Apr 21 '14 at 02:47

1

More simple solution with same output:

Hi, How are you? Fine, thanks, James, Arden.

@Joseph B: Sorry I can not comment yet! So I post my answer here.

SELECT 
        REGEXP_REPLACE(
            REGEXP_REPLACE('Hi,,How are you? Fine, thanks ,, ,  ,,,, , James,Arden.', ', | ,', ','), --Replace ', ' and ' ,' with ','
        ',{1,}', ', ') single_comma_text --Replace one or more comma with comma followed by space
FROM DUAL;

You can check this SQLFiddle

answered Apr 21 '14 at 02:47

MinhD

1,790
11
14

Great. How can I return those records that have consecutive commas two or more eg ,,? regardless of the spaces between them: , , – user311509 Apr 21 '14 at 06:30
Replace all space between commas, then use REGEXP_LIKE in WHERE clause to check if the String have 2 or more consecutive commas, ignore space: `WHERE REGEXP_LIKE(REGEXP_REPLACE(multi_commas_column, ', | ,', ','), ',{2,}')`. Check this [SQLFiddle](http://www.sqlfiddle.com/#!4/d41d8/28101) – MinhD Apr 21 '14 at 07:04
@user311509: Could you please accept this answer? This will help others with the same problem. – MinhD Apr 22 '14 at 06:10

score 0 · Answer 2 · answered Apr 21 '14 at 02:19

You can use the following query ...

SELECT 
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE('Hi,,How are you? Fine, thanks ,, ,  ,,,, , James,Arden.', ', | ,', ','), --Replace ', ' and ' ,' with ','
        ',{2,}', ', '), --Replace 2 or more occurrences of comma with single comma followed by space
    ',(.)', ', \1') single_comma_text --Replace comma followed by any character with comma followed by space followed by character
FROM DUAL;

to get the following output:

Hi, How are you? Fine, thanks, James, Arden.

Here's the SQL Fiddle.

References:

Use SQL to Replace Multiple Commas in a String with a Single Comma

2 Answers2

Linked