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: sqlfiddle.com/#!4/d41d8/28054

PM 77-1
– PM 77-1

2014-04-21 01:24:23 +00:00
Commented Apr 21, 2014 at 1:24 — PM 77-1
– PM 77-1, Commented Apr 21, 2014 at 1:24

MinhD · Accepted Answer · 2014-04-21 02:47:54Z

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, 2014 at 2:47

MinhD

1,84812 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user311509 Over a year ago

Great. How can I return those records that have consecutive commas two or more eg ,,? regardless of the spaces between them: , ,

MinhD Over a year ago

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

MinhD Over a year ago

@user311509: Could you please accept this answer? This will help others with the same problem.

Joseph B · Accepted Answer · 2014-04-21 02:19:57Z

0

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:

answered Apr 21, 2014 at 2:19

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related