Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pooja.

Asked: October 27, 2008 - 5:49 pm UTC

Last updated: July 22, 2020 - 5:19 am UTC

Version: 9

Viewed 1000+ times

You Asked

Hi ,

I have string like the following.
1.,Accenture, , ,EDS,Sun,Training Partner
2.United States,,,,
3.Canada,

I want to have the string like the following as a result of the query.
1. Accenture,EDS,Sun,Training Partner
2. United States
3. Canada

Help is appreciated.

_PS

Here is the script to create table and insert script.
create table temps
(val varchar2(1000));

insert into temps values (',Accenture, , ,EDS,Sun,Training Partner');
insert into temps values ('United States,,,, ');
insert into temps values ('Canada,');
insert into temps values (',Canada ');
commit;

and Tom said...

ops$tkyte%ORA10GR2> select val, trim( both ','  from regexp_replace( replace( trim(val), ' ,', ',' ), '(,){2,}', '\1') ) a
  2    from temps
  3  /

VAL                                     A
--------------------------------------- ---------------------------------------
,Accenture, , ,EDS,Sun,Training Partner Accenture,EDS,Sun,Training Partner
United States,,,,                       United States
Canada,                                 Canada
,Canada                                 Canada



based on
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13912710295209#29262542285517

you could play with regular expressions and perhaps make it more terse

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One less call...

A reader, November 25, 2008 - 1:43 pm UTC


Using '( *,){2,}' should allow elimination of the replace() call.

Ooops...

A reader, November 25, 2008 - 1:44 pm UTC


It won't get rid of the spaces for you...ignore.

Using Oracle 9i R2

Rajeshwaran, Jeyabal, November 25, 2008 - 2:03 pm UTC

Tom,

Can you please show me how the same can be achieved using Oracle 9i? i am currently working in Oracle 9i R2.

Tom Kyte
November 25, 2008 - 2:20 pm UTC

use the old triple replace trick:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13912710295209#13998996415614

that one does spaces, you can make it do commas.

Any other approch other than this in 9iR2

Rajeshwaran, Jeyabal, November 25, 2008 - 2:22 pm UTC

Hi Tom,
I have one approch.
scott@AFRSQA> SELECT LTRIM(SYS_CONNECT_BY_PATH(STR, ','), ',')
  2  FROM
  3   (
  4    SELECT RNUM                                               ,
  5     STR                                                      ,
  6     ROW_NUMBER() OVER(PARTITION BY RNUM ORDER BY LVL) ROW_NUM,
  7     COUNT(*) OVER(PARTITION BY RNUM) CNT
  8    FROM
  9     (
 10      SELECT LEVEL LVL                                                                                                 ,
 11       RNUM                                                                                                            ,
 12       TRIM(SUBSTR(VAL, INSTR(VAL, ',', 1, LEVEL)+1, INSTR(VAL, ',', 1, LEVEL+1) - INSTR(VAL, ',', 1, LEVEL)-1)) AS STR,
 13       LEAD(LEVEL, 1, 0) OVER(PARTITION BY RNUM ORDER BY LEVEL) LEAD
 14      FROM
 15       (
 16        SELECT ROWNUM RNUM,
 17         ','
 18         ||VAL
 19         ||',' AS VAL
 20        FROM TEMPS
 21       )
 22       CONNECT BY INSTR(VAL, ',', 1, LEVEL) > 0
 23     )
 24    WHERE STR IS NOT NULL
 25    AND LVL   != LEAD
 26   )
 27  WHERE ROW_NUM  = CNT START
 28  WITH ROW_NUM   = 1 CONNECT BY PRIOR ROW_NUM = ROW_NUM -1
 29  AND PRIOR RNUM = RNUM
 30  /

LTRIM(SYS_CONNECT_BY_PATH(STR,','),',')
-----------------------------------------------------------------------------------------------------------------------------------
Accenture,EDS,Sun,Training Partner
United States
Canada
Canada

is there is other simpler approch other than this in 9i Tom?

Tom Kyte
November 25, 2008 - 3:08 pm UTC

i already pointed you to the triple replace trick above...

Gud Approach o

A reader, November 29, 2008 - 3:05 pm UTC


Answer for the original post without the inner replace and trim

Logan Palanisamy, March 03, 2010 - 4:41 pm UTC


SQL>create table t (val varchar2(1000));

Table created.

SQL>
SQL>insert into t values (',Accenture, , ,EDS,Sun,Training ');

1 row created.

SQL>insert into t values ('United States,,,, ');

1 row created.

SQL>insert into t values ('Canada,');

1 row created.

SQL>insert into t values (',Canada ');

1 row created.

SQL>select val,
  2  trim( both ','     from regexp_replace( replace( trim(val), ' ,', ',' ), '(,){2,}', '\1') ) a,
  3  trim(',' from regexp_replace(val, '( *, *){2,}', ',')) b
  4  from t;

VAL                               A                           B
--------------------------------- --------------------------- ---------------------------
,Accenture, , ,EDS,Sun,Training   Accenture,EDS,Sun,Training  Accenture,EDS,Sun,Training
United States,,,,                 United States               United States
Canada,                           Canada                      Canada
,Canada                           Canada                      Canada

I have a simple way to remove multiple commas in the whole string

Leena Chittivelu, July 20, 2020 - 1:58 pm UTC

Here is the simple one to suffice all the below cases:
1> Removing multiple commas in starting of the string
2> Removing multiple commas at the end of the string
3> Removing multiple commas to single comma in the middle of the string

select REGEXP_REPLACE(REGEXP_REPLACE(',,LE,,EN,,A,,,','^,*|,*$',''),',{1,}', ', ')

output :

LE, EN, A
Connor McDonald
July 22, 2020 - 5:19 am UTC

nice stuff

IT WORKED!!

A reader, January 17, 2021 - 1:12 pm UTC

select val, trim( both ',' from regexp_replace( replace( trim(val), ' ,', ',' ), '(,){2,}', '\1') ) a

IT WORKED LIKE A CHARM