REGEXP_REPLACE in Oracle

Question

I need to use REGEXP_REPLACE to do the following :

 If word starts with 'ABCD' then replace first four(4) chars with 'FFFF'
    else
 If word starts with 'XYZ' then replace first three(3) chars with 'GGG'

How do I use REGEXP_REPLACE to do conditional replace ?

Is the use of REGEXP_REPLACE part of the problem statement, or only part of the solution you want to offer? The best answer (given by Gordon Linoff) does not use it. — user5683823
– user5683823, Commented Jun 4, 2019 at 14:20

Gordon Linoff · Accepted Answer · 2019-06-04 14:14:58Z

1

You can use case and string operations:

select (case when word like 'ABCD%'
             then 'FFFF' || substr(word, 5)
             when word like 'XYZ%'
             then 'GGG' || substr(word, 4)
             else word
        end) as new_word

answered Jun 4, 2019 at 14:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Radagast81 · Accepted Answer · 2019-06-04 14:18:23Z

1

If it has to be REGEXP_REPLACE you'll have to combine two function calls:

REGEXP_REPLACE( 
  REGEXP_REPLACE(word,'^ABCD','FFFF')
  ,'^XYZ', 'GGG')

But I would prevere Gordon's caseapproach...

answered Jun 4, 2019 at 14:18

Radagast81

3,0461 gold badge9 silver badges21 bronze badges

Collectives™ on Stack Overflow

REGEXP_REPLACE in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related