Conditional regexp_replace Oracle / PLSQL

Question

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?

You can only conditionally replace in Boost regex. No way with Oracle regex. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 15, 2018 at 16:58
Can't you use multiple expressions like Bigs Dog|Wolf|Dingo, and Smalls like Cat|Persian, and Mediums Donkey|Horse ? If you can't do a callback, then It can't be done all at once. Of course you can also add boundary's around the alternations, like word \b(list)\b and whitespace (?<!\S)(list)(?!\S) if needed. — user557597
– user557597, Commented Jun 15, 2018 at 17:46

Gordon Linoff · Accepted Answer · 2018-06-15 16:57:39Z

3

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.

answered Jun 15, 2018 at 16:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

4 Comments

Squashy Josh Over a year ago

I completely agree! It's to solve an office argument of possibility. :)

Wiktor Stribiżew Over a year ago

If one word is a substring of another there may be problems with this approach.

Gordon Linoff Over a year ago

@WiktorStribiżew . . . And that would be true of the OP's original regular expression as well.

Wiktor Stribiżew Over a year ago

@GordonLinoff No, because a regex only processes a string once.

shrek · Accepted Answer · 2018-06-15 17:38:16Z

1

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

answered Jun 15, 2018 at 17:38

shrek

8876 silver badges12 bronze badges

Comments

LukStorms · Accepted Answer · 2018-06-15 18:35:33Z

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]

Collectives™ on Stack Overflow

Conditional regexp_replace Oracle / PLSQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related