Boneist February 2016

How to remove repeated commas and trim from each end using REGEXP_REPLACE?

I need to concatenate several fields together, which may or may not be null. I could potentially end up with a string like: ',,c,,e,,' which I actually want to show as 'c,e'.

I can get this via a combination of regexp_replace and trim:

with sd as (select 'a,b,c' str from dual union all
            select 'a' str from dual union all
            select null str from dual union all
            select 'a,,,d' from dual union all
            select 'a,,,,e,f,,'from dual union all
            select ',,,d,,f,g,,'from dual)
select str,
       regexp_replace(str, '(,)+', '\1') new_str,
       trim(both ',' from regexp_replace(str, '(,)+', '\1')) trimmed_new_str
from   sd;

STR         NEW_STR     TRIMMED_NEW_STR
----------- ----------- ---------------
a,b,c       a,b,c       a,b,c          
a           a           a              

a,,,d       a,d         a,d            
a,,,,e,f,,  a,e,f,      a,e,f          
,,,d,,f,g,, ,d,f,g,     d,f,g  

but I feel like it ought to be doable in a single regexp_replace only I can't work out for the life of me how it could be done!

Is it possible? If so, how?

Answers


Jorge Campos February 2016

Give it a try:

with sd as (select 'a,b,c' str union all
            select 'a' union all
            select null union all
            select 'a,,,d' union all
            select 'a,,,,,e,f,,' union all
            select ',,,d,,f,g,,')
select str,
       regexp_replace(str, '(,){2,}', '\1', 1, 0) new_str,
       trim(both ',' from regexp_replace(str, '(,){2,}', '\1', 1, 0)) trimmed_new_str
from   sd;


Noel February 2016

Query:

with sd as (select 'a,b,c' str from dual union all
            select 'a' from dual  union all
            select null from dual union all
            select 'a,,,d,' from dual  union all
            select ',a,,,d' from dual  union all
            select ',a,,,d,' from dual  union all
            select ',,,a,,,d,,,' from dual  union all
            select ',a,,,,,e,f,,' from dual union all
            select ',,d,,f,g,,' from dual )
select str,
       regexp_replace(str, '^,+|,+$|,+(,\w)','\1') new_str
from   sd;

Result:

str             new_str
-----------------------
a,b,c           a,b,c
a               a
(null)          (null)  
a,,,d,          a,d
,a,,,d          a,d
,a,,,d,         a,d
,,,a,,,d,,,     a,d
,a,,,,,e,f,,    a,e,f
,,d,,f,g,,      d,f,g

Pattern:

  ^,+       matches commas at the beginning
  |         OR
  ,+$       matches commas at the end
  |         OR
  ,+(,\w)   matches several commas followed by a single comma and a word.

Replaces above with only first sub expression, which is a comma and word.

Post Status

Asked in February 2016
Viewed 2,042 times
Voted 10
Answered 2 times

Search




Leave an answer