Another substitution problem

Shell Programming and Scripting
1

Hello again,

I'm trying to change the following line:

 
INSERT INTO PH1_TX_LOAD VALUES ('TX-78731-AABSS:4182-4','RH: GUIDE TO TENNIS',TO_DATE('18-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'),TO_DATE('21-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'),500)

so that any TO_DATE is taken out and just the date left, i.e

TO_DATE('18-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS')

to

'18-JUN-2001'

I have tried the following:

sed "s/TO_DATE(\(*:\)/\1/g"  inputfile

Which doesn't work, what are the errors with it?

2

Your insert query contains 2 occurrence of TO_DATE statements. So, if you will use g at the end of sed command to date value after TO_DATE then it will return the second date(i.e 21-JUN-2001). Because, the sed parser checks the greedy pattern matching.

So don't include g at the end. Try following command

sed "s/TO_DATE('\(.*\)'/\1/" inputfile | cut -d":" -f1
3

If I don't include g will only the first instance be changed?
Essentially I want to change:

INSERT INTO PH1_TX_LOAD VALUES ('TX-78731-AABSS:4182-4','RH: GUIDE TO TENNIS',TO_DATE('18-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'),TO_DATE('21-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'),500)

to

INSERT INTO PH1_TX_LOAD VALUES ('TX-78731-AABSS:4182-4','RH: GUIDE TO TENNIS','18-JUN-2001','21-JUN-2001', 500)

When I use

sed "s/TO_DATE('\(.*\)'/\1/" inputfile > output

it appears that I need to remove the of the string in the brackets as it comes up with

 
18-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS')
4 
sed "s/^\(.*\),TO_DATE('\([0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9]\).*),TO_DATE('\([0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9]\).*')\(.*\)/\1,'\2','\3'\4/g" inputfile
5

If date format is fixed and always like blow:

TO_DATE('18-JUN-2001:00:00:00', 'DD-MON-YYYY:HH24:MI:SS')

i.e. 57 characters overall then

sed "s/TO_DATE('\(...........\):\(....................................\)/\1/g" inputFile

Here dots in red are for date value (18-JUN-2001) - one dot for each character.
dots in green are for 00:00:00', 'DD-MON-YYYY:HH24:MI:SS').

6 
sed "s/TO_DATE(\('[^:]*\)[^)]*)/\1'/g" infile
7

Much appreciated. Starting to get to grips with all this