CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user311509
user311509

Reputation: 2866

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

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.

Upvotes: 1

Views: 4003

Answers (2)

MinhD
MinhD

Reputation: 1810

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

Upvotes: 1

Joseph B
Joseph B

Reputation: 5669

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:

  1. REGEXP_REPLACE on Oracle® Database SQL Reference
  2. Multilingual Regular Expression Syntax on Oracle® Database SQL Reference

Upvotes: 0

Related Questions