IntelliJ IDEA: regex for removing Oracle to_timestamp syntax.

Question

I'm trying to come up with a search/replace expression that will convert from Oracle style inserts with timestamp fields to insert statements for another database.

Basically, I want to convert strings like:

to_timestamp('13-SEP-09 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM')

to just:

'13-SEP-09 12.00.00.000000000 PM'

I've tried several expressions in the IDEA's search/replace box, but I still can't quite get it. This one:

to_timestamp(.[^,]*,.[^)]*)

replaced with $1 ends up matching the string I want except the close parenthesis, but then only deletes the first part. I end up with:

('13-SEP-09 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM')

I really don't understand what's happening here.

Answer 1

Looks like you want:

to_timestamp\(('[^']*')[^)]*\)

Breaking it down:

to_timestamp is obvious

\( matches the opening paren

( starts capturing

'[^']' matches the first quoted string

) stops capturing

[^)]*\) matches the remaining text

If that's the whole string you're matching, and not just part of a larger text, you can use .* instead of [^)]*\) for the last part; you don't really care what comes after the closing '.