UltraEdit --> Append option

Question

I have a huge list of updates to the Oracle DB. This is some prod-support work.

My sample update goes like this

update XYZ 
  set  name = 'abb',
       job  = 'mgr'
where joining_date = to_date('2015-02-11'
  and job_id in (....list of job_id this can be anywhere in 1000s...);



update XYZ
  set  name = 'jab',
       job  = 'appdev'
where joining_date = to_date('2016-03-10'
  and job_id in (....list of job_id this can be anywhere in 1000s...);

Based on the joining dates and the job_ids there are several updates. The list is on and on and on.

What's really missing here is the date format the 'yyyy-mm-dd'. I'm using UltraEdit. That's the only editor my client has provided. I'll have to append this date format to the dates.

I tried Find and Replace with regular expression

Find [0-9]
Replace ','yyyy-mm-dd'

If I do this the last Number in the date is also getting replaced.

I have SQL developer, if we can achieve this in SQL developer that'll be great as well.

Answer 1

If I understand the question correctly, you are looking for a way to append the text 'yyyy-mm-dd' to all date statements, so that the 2 examples would look like this:

update XYZ 
  set  name = 'abb',
       job  = 'mgr'
where joining_date = to_date('2015-02-11','yyyy-mm-dd')
  and job_id in (....list of job_id this can be anywhere in 1000s...);



update XYZ
  set  name = 'jab',
       job  = 'appdev'
where joining_date = to_date('2016-03-10','yyyy-mm-dd')
  and job_id in (....list of job_id this can be anywhere in 1000s...);

If that is right, you can do a Search&Replace operation with the following options:

• Find what: to_date\(('[\-0-9]*')
• Replace with: to_date($1,'yyyy-mm-dd')
• Check Regular expressions and select Perl