AmnonM has asked for the wisdom of the Perl Monks concerning the following question:

Hi I'm looking for a script that can scan an SQL file and change all the 
IF(expr1,expr2,expr3)

[download]
to 
CASE 
 WHEN condition THEN result 
 [ WHEN condition THEN result ]... 
 [ ELSE result ] 
END

[download]
or sometimes 
CASE expression 
 WHEN value THEN result 
 [ WHEN value THEN result ]... 
 [ ELSE result ] 
END

[download]

Complexity comes in if any of the expr's have commas and brackets, and even nested IFs. This must be a challenge even to people expert in regular expressions.

(The full story is that I'm planning to move from MySQL to Vertica, so other changes might also need to me made. But save this for another time.)

Does anyone know where to start?

Amnon

Replies are listed 'Best First'.
Re: "if" to "case ... end"
by LanX (Saint) on Oct 31, 2013 at 10:41 UTC
    Did you think about using an existing SQL parser like SQL::Statement::Structure ?

    searching gives plenty of hits for "SQL parser Perl" also in PM's archives...

    Cheers Rolf

    ( addicted to the Perl Programming Language)

[reply]
Re: "if" to "case ... end"
by hdb (Monsignor) on Oct 31, 2013 at 09:48 UTC

    Without you specifying in more detail how expr1,expr2,expr3 are to be plugged into your CASE...END statements, I can only offer this 

    while(<>){
    if( /IF\(expr1,expr2,expr3\)/ ) { 
        if( rand() > 0.1 ) {
            s/IF\(expr1,expr2,expr3\)/CASE\nWHEN condition THEN result
+\n[ WHEN condition THEN result ]...\n[ ELSE result ]\nEND\n/;
        } else {
            s/IF\(expr1,expr2,expr3\)/CASE expression\nWHEN value THEN
+ result\n[ WHEN value THEN result ]...\n[ ELSE result ]\nEND\n/;
        }
    }
}

    [download]
[reply]
[d/l]
[select]
Re: "if" to "case ... end"
by Laurent_R (Canon) on Oct 31, 2013 at 10:37 UTC
[reply]

Back to Seekers of Perl Wisdom