Nested regular expression replacement in python

Question

I'm migrating SQL from one platform to another. The SQL contains DECODE statements, which are not supported by my target platform.

I'm using regular expressions to translate the decode statements to case statements but I'm failing at nested decodes:

import re
sql_frag = "select decode(dim1,'','-',dim1) as myfield1, decode(dim2,'','-',dim2') as myfield2"
reg=re.compile("(decode\((.*?),(.*?),(.*?),(.*?)\))",re.IGNORECASE)

matches = reg.findall(sql_frag)
for match in matches:
sql_frag = sql_frag.replace(match[0],'case when %s = %s then %s else %s end' % (match[1],match[2],match[3],match[4]))

will match all occurrences of decode in

select decode(dim1,'','-',dim1) as myfield1, decode(dim2,'','-',dim2') as myfield2

and will replace with case statements:

select case when dim1 = '' then '-' else dim1 end as myfield1, case when dim2 = '' then '-' else dim2' end as myfield2

But the code trips up on nested decode statements:

sql_frag="select decode(f1,3,4,decode(f2,5,4,f2)) as myfield, decode(foo,bar,baz,foo) as myfield2"

>>> reg.findall(sql_frag)
[('decode(f1,3,4,decode(f2,5,4,f2)', 'f1', '3', '4', 'decode(f2,5,4,f2'), ('decode(foo,bar,baz,foo)', 'foo', 'bar', 'baz', 'foo')

and returns

select case when f1 = 3 then 4 else decode(f2,5,4,f2 end) as myfield, case when foo = bar then baz else foo end as myfield2

Is there a way to process the innermost decode before the others so that I can cleanly replace all of the decode statements with case statements?

Hmmm... So what's with Oracle?

NullUserException
– NullUserException

2011-09-14 18:22:37 +00:00
Commented Sep 14, 2011 at 18:22 — NullUserException
– NullUserException, Commented Sep 14, 2011 at 18:22
neither database is Oracle :D

Neil Kodner
– Neil Kodner

2011-09-14 18:52:33 +00:00
Commented Sep 14, 2011 at 18:52 — Neil Kodner
– Neil Kodner, Commented Sep 14, 2011 at 18:52
Interesting. What DB supports DECODE besides Oracle?

NullUserException
– NullUserException

2011-09-14 19:14:19 +00:00
Commented Sep 14, 2011 at 19:14 — NullUserException
– NullUserException, Commented Sep 14, 2011 at 19:14

Tomalak · Accepted Answer · 2011-09-14 18:26:20Z

1

Is there a way to process the innermost decode before the others so that I can cleanly replace all of the decode statements with case statements?

Yes.

Use ((?![^)]*decode).*?) in place of any (.*?) where a nested DECODE can legally occur. Run the regex in a loop.

Be aware that this can fail if there are strings (i.e. "THEN values") that contain the word "decode". If you know your data and can rule out this case, the regex approach above will be good-enough for your one-off use case.

answered Sep 14, 2011 at 18:26

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

Collectives™ on Stack Overflow

Nested regular expression replacement in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related