Regex to replace TO_DATE in string

Question

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.

Are you asking how to replace the values in the table_a? Or you asking how to modify the query? — mmtauqir
– mmtauqir, Commented Jun 12, 2012 at 3:36
I'm guessing the sql query is a value in a VARCHAR2 field and Nik wants to replace the to_date text with sysdate. So it really has nothing to do with the query, it's just a regexp question. — kentcdodds
– kentcdodds, Commented Jun 12, 2012 at 3:39
Sorry if I wasn't clear. kentcdodds is correct. The sql statement is a value in a VARCHAR2 field in a table. So, how can I use regexp_replace in my query to replace to_date with sysdate? — sukach
– sukach, Commented Jun 12, 2012 at 3:46

dinox0r · Accepted Answer · 2014-07-21 15:49:47Z

2

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

edited Jul 21, 2014 at 15:49

answered Jun 12, 2012 at 3:54

dinox0r

16.1k4 gold badges39 silver badges43 bronze badges

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

1 Comment

sukach Over a year ago

Thanks, this pointed me in the write direction. My final statement was: SELECT regexp_replace(sql_stmt, 'to_date\([A-Za-z0-9:'' ,-]+\)', 'SYSDATE') FROM sample_table;

Michael Scholz · Accepted Answer · 2012-06-12 08:37:34Z

1

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 ..

answered Jun 12, 2012 at 8:37

Michael Scholz

211 bronze badge

1 Comment

sukach Over a year ago

There can be multiple occurrences of to_date, however, thinking about it, I would probably be ok just replacing the first occurrence.

Feng Zhang · Accepted Answer · 2020-06-08 13:49:49Z

0

I used regular expression to pick out the to_date portion and replace with 'sysdate': 'to_date[^)]*)'

answered Jun 8, 2020 at 13:49

Feng Zhang

2,0001 gold badge21 silver badges22 bronze badges

Collectives™ on Stack Overflow

Regex to replace TO_DATE in string

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related