Regex to replace TO_DATE in string

I have a string that has a TO_DATE function in it. I would like to replace this TO_DATE call with SYSDATE.

For example, my string is basically a SQL insert or update, so I have something like

INSERT INTO table_a (col_a, col_b, col_c, updt_dt) VALUES ('A', 'B', 'C', to_date('2012-06-11 22:10:44', 'YYYY-MM-DD HH24:MI:SS'));

And I want to replace the TO_DATE call with this:

INSERT INTO table_a (col_a, col_b, col_c, updt_dt) VALUES ('A', 'B', 'C', SYSDATE);

Remember, this entire insert statement is in one database field. So, how could I use regexp_replace to replace the TO_DATE call with SYSDATE?

I am running Oracle 10gR2.

Answers


Using sed on linux:

sed -e "s/to_date([A-Za-z0-9:' ,-]\+)/sysdate/g" <<< "INSERT INTO table_a (col_a, col_b, col_c, updt_dt) VALUES ('A', 'B', 'C', to_date('2012-06-11 22:10:44', 'YYYY-MM-DD HH24:MI:SS'));"

If you're not using linux, the find/replace regexp is:

s/to_date([A-Za-z0-9:' ,-]+)/sysdate/g

It should be compatible with most of the regexp engines used by editors


If your string contains only one to_date function and if the to_date function has always the same length, then you can use instr to get the position of the first occurence of to_date.

Your statement would roughly look like that:

update ... set field = substr(field, 1, instr(..)-1) || 'sysdate' || substr(field, instr(..)+99) where ..


Need Your Help

Capybara::ElementNotFound error when for xpath

ruby xpath cucumber capybara

I'm running into an issue where I'm running the following capybara/cucumber code:

Directing error output from Bash psql

bash amazon-redshift psql

I'm using psql to connect to a Redshift database. Redshift doesn't seem to support \set ON_ERROR_STOP TRUE so I can't use that to stop the query and direct something to error output.