Perl Regex for converting query strings

Question

I need to convert some Oracle query strings to be Postgres compatible. Some of the queries use the Oracle TO_NUMBER function which isn't valid in Postgres. So I need to remove all occurrences of the TO_NUMBER function in the sql queries, and replace with an explicit cast to integer...

Here is an example SQL to be converted:

select coalesce (sum (count), 0) as count from mytable where
date_code_id between to_number(to_char(current_date-INTERVAL '1
day','yyyymmdd')) and to_number(to_char(current_date-INTERVAL
'1 day','yyyymmdd'))

After conversion the result should be this:

select coalesce (sum (count), 0) as count from mytable where
date_code_id between to_char(current_date-INTERVAL '1
day','yyyymmdd')::integer and to_char(current_date-INTERVAL '1
day','yyyymmdd')::integer

There could potentially be any number of nested functions within the TO_NUMBER function so it has to account for that. It seems like a job for regex (needs to be Perl regex, since the tool I am using for this only supports Perl regex). The problem is I am a regex idiot.

Can anyone show me how this can be done with regex?

Or even better yet does anyone know of a good SQL translator. I found a few, but they are interactive online tools. I would like something I could send a batch of queries for conversion.

Answer 1

This is not a good use of regexes. You could probably do it with Perl regexes (only because they stopped being "regular" some time ago!), but it's still not a good idea.

This is a job for SQL::Translator.