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.

You could append the string quite easily in Excel. Or, if you don't have Office / OpenOffice / LibreOffice, you can use Google Docs online. — AJPerez, May 20 '16 at 12:08
@TANUJA-PINGALI I have read the question 3 times and have nevertheless not understood what you want to do with UltraEdit. It is simple to run a regular expression replace in UE. But I have not understood what you want to find and what should be the result of the replace. Please edit your question to make it possible for me to help you. Post a block containing how the file looks before and one more block showing us how this block should look after the regular expression replace. — Mofi, May 21 '16 at 10:21
@Mofi. I'm not sure what's not there to understand here. There are a series of updates. I have to just "append" the date format to the to_dates in those updates. Replace will replace which ever character I choose. It will not "append" the text. I hope you know what is append. I thought there is way to do it in UE. Except for writing a JavaScript , there seems no other possible way. If you have something better to add here, then gr8. Thank you so far for replying back — TANUJA PINGALI, May 23 '16 at 06:03
@AJPerez. Thank you for your reply. I tried Excel it did not work. It is possible there but the rest of the formatting is going haywire. So I'm sticking with UE. When it comes to 600 date changes to 600000 rows of update statement formatting. I think I'll choose 600 rows of manual date changes. Thanks anyway. We all just try. — TANUJA PINGALI, May 23 '16 at 06:06
@TANUJAPINGALI: if your question is answered with my reply, could you pls. be so kind to accept the answer (tick checkmark nxt to answer), so that the question is no longer shown as "Open"? — MBaas, May 31 '16 at 08:52

score 1 · Accepted Answer · edited May 24 '16 at 16:15

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

edited May 24 '16 at 16:15

Mofi

46,139
17
80
143

answered May 24 '16 at 10:17

MBaas

7,248
6
44
61

1

Posting a block showing file content before and the same block after execution of replace command makes everything very clear. I thought all the time that `'yyyy-mm-dd'` is a pattern for the replace with a date to apply from a list file whereby first date found should be replaced by first date in list file, second date found should be replaced by second date in list file, etc. But it is indeed just a simple regular expression replace using a backreference. Thanks for this well written answer explaining also the task which I could not understand from question. – Mofi May 24 '16 at 16:23

UltraEdit --> Append option

1 Answers1