How to replace a single number from a comma separated string in oracle using regular expressions?

Question

I have the following set of data where I need to replace the number 41 with another number.
column1
41,46
31,41,48,55,58,121,122
31,60,41
41

We can see four conditions here
41,
41
,41,
41,

I have written the following query

REGEXP_replace(column1,'^41$|^41,|,41,|,41$','xx')

where xx is the number to be replaced.
This query will replace the comma as well which is not expected.

Example : 41,46 is replaced as xx46. Here the expected output is xx,46. Please note that there are no spaced between the comma and numbers.

Can somebody help out how to use the regex?

Why are you storing your data in such a CSV format? As you can see, it makes it very hard to work with and maintain your data. Much better it would be to have each number in the CSV list on a separate row. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 4, 2017 at 6:00

Gurwinder Singh · Accepted Answer · 2017-07-04 16:24:57Z

2

Assuming the string is comma separated, You can use comma concatenation with replace and trim to do the replacement. No regex needed. You should avoid regex as the solution is likely to be slow.

with t (column1) as (
    select '41,46'                  from dual union all
    select '31,41,48,55,58,121,122' from dual union all
    select '31,60,41'               from dual union all
    select '41'                     from dual
)
-- Test data setup. Actual solution is below: --

select
    column1,
    trim(',' from replace(','||column1||',', ',41,', ',17,')) as replaced
from t;

Output:

COLUMN1                 REPLACED
41,46                   17,46
31,41,48,55,58,121,122  31,17,48,55,58,121,122
31,60,41                31,60,17
41                      17

4 rows selected.

Also, it's worth noting here that the comma separated strings is not the right way of storing data. Normalization is your friend.

edited Jul 4, 2017 at 16:24

answered Apr 4, 2017 at 5:58

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

1 Comment

Binary Terror Over a year ago

Thanks. Works perfectly.

Collectives™ on Stack Overflow

How to replace a single number from a comma separated string in oracle using regular expressions?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related