Shell Programming and Scripting

Hi,

I have a file with occurances of the string "TO_DATE(<number here>,'J')" at random places. I need minus 2400000 from the number and replace the string with "convert(date, dateadd(dd, <new number here>,'16 Nov 1858')".

I'm finding this difficult as the string isn't necessarily in the same places all the time and I'm not sure how to go about taking the number out and performing a calculation on it

Some examples of the original file are:

Code:

INSERT INTO EMPLOYEE VALUES
    (7505,'DOYLE','JEAN','K',671,7839,TO_DATE(2446160,'J'),2850,NULL,13)
go
INSERT INTO PRICE VALUES
   (100871,4.8,3.2,TO_DATE(,'J'),TO_DATE(2447862,'J'))
go

So in the first example I need to take the 2446160 out and minus 2400000 from it, then replace the whole "TO_DATE(...)" with "convert(date, dateadd(dd, <new number here>,'16 Nov 1858')"

I've been looking at using sed and maybe even grep and cut but I'm stumped.

Code:

 
sed "s/TO_DATE(\([0-9]*\),'J')/convert(date, dateadd(dd,FSXXXX\1FSXXXX,'16 Nov 1858')/g" inputFile | awk 'FS="FSXXXX" {$2=$2-24400000;print;}'

Thanks for the reply.
I'm not sure if awk works on this system, I get the errors:
awk: syntax error near line 1
awk: bailing out near line 1

even awk {print $0} doesn't seem to work

Line borrowed from one of "scottn"'s posts.

ok.. I see that error on solaris. Pls try this..

Code:

sed "s/TO_DATE(\([0-9]*\),'J')/convert(date, dateadd(dd,FSXXXX\1FSXXXX,'16 Nov 1858')/g" inputFile | nawk 'FS="FSXXXX" {if($2+0 > 0)$2=$2-24400000;print;}'

Use nawk instead of awk.

Thanks, that works for the most part although -2400000 seems to be replaced in strange places and the calculation hasn't been done eg:

Code:

INSERT INTO PRICE VALUES -2400000
   (100890,54,40.5,convert(date, dateadd(dd, 47314 ,'16 Nov 1858'),convert(date, dateadd(dd, 2447527 ,'16 Nov 1858'))
go -2400000
 -2400000

Corrected in earlier post [add if condition]. That should give right result now.
Not much familierity with nawk yet. Others may tell if anything better is there.