Oracle Regexp_replace multiple occurrence

Question

Hi I want to append a letter C to a string if it starts with a number . Also if it has any punctuation then replace with underscore _ Eg : 5-2-2-1 ==> C5_2_2_1

I tried ,but I am not able to replace the multiple occurrence of the punctuation. I am missing some simple thing, I cant get it.

SELECT  REGEXP_REPLACE('9-1-1','^(\d)(-),'C\1_' ) FROM DUAL;

Community · Accepted Answer · 2017-05-23 12:33:42Z

2

SELECT case when REGEXP_LIKE('9-1-1','^[[:digit:]]') then 'C' END 
       || REGEXP_REPLACE('9-1-1', '[[:punct:]]', '_')
FROM DUAL;

[:digit:] any digit
[:punct:] punctuation symbol

if you have a lot of rows with different values then try to avoid regex:

SELECT case when substr('9-1-1',1,1) between '0' and '9' then 'C' end
       || translate('9-1-1', ',.!-', '_')
FROM DUAL;

Check here for example: Performance of regexp_replace vs translate in Oracle?

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Dec 2, 2014 at 22:33

Multisync

8,8271 gold badge20 silver badges20 bronze badges

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

3 Comments

Rajiv A Over a year ago

Thanks a lot it works! I was just trying to minimize multiple functions because I'll be scanning this through millions of rows

Multisync Over a year ago

@Raj A I've added some more info. I didn't know about millions of rows

Rajiv A Over a year ago

Thanks so much for your effort and the link!!

Gordon Linoff · Accepted Answer · 2014-12-02 22:33:50Z

1

Try this:

select (case when substr(val, 1, 1) between '0' and '9' then 'C' else '' end) ||
       regexp_replace(val, '([-+.,;:'"!])', '_')

answered Dec 2, 2014 at 22:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Rajiv A Over a year ago

Thanks a lot for the quick reply !!,Just out of curiosity if there a way to do it in once regular expression function

Gordon Linoff Over a year ago

@RajA . . . I don't think there is a way to do this with one function.

Rajiv A Over a year ago

..Hmmm I was thinking if anyhow we can capture more than one occurance of a character..But its okay, I will use this..Thanks again

Collectives™ on Stack Overflow

Oracle Regexp_replace multiple occurrence

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related