CODEWITHSUNDEEP
sqlregexoracle-database
Squashy Josh
Squashy Josh

Reputation: 51

Conditional regexp_replace Oracle / PLSQL

I'm trying to do a conditional replace within one regexp_replace statement.

For example, if I have the string, 'Dog Cat Donkey', I would like to be able to replace 'Dog' with 'BigDog', 'Cat' with 'SmallCat' and 'Donkey' with 'MediumDonkey' to get the following:

'BigDog SmallCat MediumDonkey'

I can do it where all are prefixed with the word Big but can't seem to make it replace conditionally.

I currently have this

select regexp_replace('Dog Cat Donkey', '(Cat)|(Dog)|(Donkey)', ' Big\1\2\3')
from dual

but of course this only returns 'BigDog BigCat BigDonkey'.

I'm aware this isn't the best way of doing this but is it possible?

Upvotes: 1

Views: 2124

Answers (3)

LukStorms
LukStorms

Reputation: 29657

For conditional replacement via REGEX_REPLACE?

Then currently you can do this by repeating it for each different replacement string.

But you could still use the | (OR) within the 1 capture group to change more than 1 word for the same replacement string.

And as Gordon Linoff pointed out.
You don't really need a REGEX_REPLACE when a normal REPLACE is sufficient to match a single word.

select regexp_replace(
         regexp_replace(
           regexp_replace( str, 
              '(Dog|Snoopy)', 'Big\1')
            ,'(Cat|Feline)', 'Small\1')
           ,'(Donkey|Ass)', 'Medium\1')
from (select 'You Ass, that is not a Dog, but a Cat on a Donkey.' as str from dual);

Returns: 

You MediumAss, that is not a BigDog, but a SmallCat on a MediumDonkey.

Do note however that when using the pipe in a regex, that the order matters. So if some words start the same then better put them in order of descending length.
Example: 

select 
regexp_replace(str, '(foo|foobar)', '[\1]') as foo_foobar,
regexp_replace(str, '(foobar|foo)', '[\1]') as foobar_foo
from (select 'foo foobar' as str from dual);

Returns: 

FOO_FOOBAR       FOOBAR_FOO
---------------  ---------------
[foo] [foo]bar   [foo] [foobar]

Upvotes: 1

shrek
shrek

Reputation: 887

Query - 

select listagg(final_str,' ') within group (order by sort_str) as output from (
SELECT 
    CASE LST
        WHEN 'Dog' THEN 'BigDog'
        WHEN 'Cat' THEN 'SmallCat'
        WHEN 'Donkey' THEN 'MediumDonkey'
    END AS final_str,
    CASE LST
        WHEN 'Dog' THEN 1
        WHEN 'Cat' THEN 2
        WHEN 'Donkey' THEN 3
    END AS sort_str
from (
SELECT
    trim(REGEXP_SUBSTR('Dog Cat Donkey', '(\S*)(\s*)', 1, LEVEL)) AS LST
FROM
    DUAL
CONNECT BY
    REGEXP_SUBSTR('Dog Cat Donkey', '(\S*)(\s*)', 1, LEVEL) IS NOT NULL
    ));

Output - 

BigDog SmallCat MediumDonkey

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Have you considered just doing multiple replace()s?

select replace(replace(replace(str, 'Dog', 'BigDog'), 'Cat', 'SmallCat'), 'Donkey', 'MediumDonkey')

I get that regexp_replace() is really powerful. And it might be able to do this. But I'm not sure that's a better solution in terms of expressing what you are doing.

Upvotes: 3

Related Questions