How to identify delimiter to find and replace a string with sed?

duke0001 · October 29, 2019, 4:23pm

I need to find and replace a date format in a SQL script with sed . The original lines are like this:

       ep.begin_date, ep.end_date, ep.facility_code,
       AND     ep.begin_date <= '01-JUL-2019'
                     ep.begin_date, ep.end_date, ep.facility_code,
       AND     ep.begin_date <= '01-JUL-2019'
                     ep.begin_date, ep.end_date, ep.facility_code,
       AND    ep.begin_date <= '01-JUL-2019'

Then I want to replace '01-JUL-2019' with variable which real value is '01-OCT-2019'. After replacement, the line in file should be like this:
AND ep.begin_date <= '01-OCT-2019'. I am not good at how to use sed to identify delimiter or separator and help sed to find right part of this string and replace it with right value. Please help with your code. My testing code is ugly so that I do not post them. I would like to learn from all of you. Thanks for help.

RudiC · October 29, 2019, 5:27pm

How specific the date regex has to be? Is it OK to match 31-FEB? Any condidtions for the year, e.g. only 20xx, or 19xx as well? Does your sed handle EREs? Try

NEWDATE=01-OCT-2019
sed -r "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9]{2}/$NEWDATE/" file
       ep.begin_date, ep.end_date, ep.facility_code,
       AND     ep.begin_date <= '01-OCT-2019'
                     ep.begin_date, ep.end_date, ep.facility_code,
       AND     ep.begin_date <= '01-OCT-2019'
                     ep.begin_date, ep.end_date, ep.facility_code,
       AND    ep.begin_date <= '01-OCT-2019'

duke0001 · October 30, 2019, 11:08am

RudiC:

Thanks for your questions and advice. My Unix is Solaris 10. It seems sed do not support EREs. Please see following output. As for date regex is concerned, this format was created by some other people and has been in production for years. I could not change it at this moment. So the best expression will still be like '01-MON-20XX' or '31-MON-20XX'. These dates are government fiscal quarter begin date and end date. It is in SQL statement. I want to use shell script to automate the change to new quarter begin date and end date for data process and report generation. You are absolutely right. $NEWDATA is variable which is queried from database and put with sed to replace old date. Here is output:

/home/oracle> sed -r "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9]{2}/"'01-OCT-2019'"/" test4.txt
sed: illegal option -- r

/home/oracle> sed -E "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9]{2}/"'01-OCT-2019'"/" test4.txt
sed: illegal option -- E

/home/oracle> sed -e "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9]{2}/"'01-OCT-2019'"/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

Now question is: based on Solaris 10, how can sed handle the extended regular expressions? Please give me some idea. Thanks.

vgersh99 · October 30, 2019, 11:19am

how about:

/home/oracle> sed -e "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9][0-9]/"'01-OCT-2019'"/" test4.txt

duke0001 · October 30, 2019, 12:13pm

vgersh99:

Thanks for input, here is the output

/home/oracle/> sed -e "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9][0-9]/"'01-OCT-2019'"/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

RudiC · October 30, 2019, 12:41pm

OK, then, try to write the months verbatim explicitly and escape parentheses, pipes, and braces:

sed  "s/[0-3][0-9]-\(JUN\|JUL\|AUG\)-[12][09][0-9]\{2\}/$NEWDATE/" file

duke0001 · October 30, 2019, 3:35pm

RudiC:

To write months verbatim and escape, it didn't work either. I need sed identify months because 4 quarters have 4 different begin date and 4 end date. Hard code will not work. I tried something. Please see all output below. Here, $NEWDATE=01-OCT-2019.

/home/oracle> sed  "s/[0-3][0-9]-\(JUN\|JUL\|AUG\)-[12][09][0-9]\{2\}/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

/home/oracle/> sed  "s/[0-3][0-9]\-[a-zA-Z]{3}\-[12][09][0-9]\{2\}/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

RudiC · October 30, 2019, 3:38pm

Does your OS provide other sed versions?

For debugging, try to restrict yourself to match the months only. You may want to apply vgersh99's proposal as well to get rid of the {2} .

duke0001 · October 30, 2019, 4:25pm

RudiC:

Thanks for suggestion, I tried. It output the same result. I know the Date format in our SQL script is not configured well. But I cannot make change at this time. It will cause many changes. On our Solaris 10, we have /usr/xpg4/bib/sed , can this sed work?
These two use vgersh99's proposal and without {2}

/home/oracle> sed -e "s/[0-3][0-9]-($(locale abmon | tr 'a-z;' 'A-Z|'))-[12][09][0-9][0-9]/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

/home/oracle> sed  "s/[0-3][0-9]\-[a-zA-Z]{3}\-[12][09][0-9][0-9]/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

I am working on how to restrict to match the month. Thanks for your help.

RudiC · October 30, 2019, 4:29pm

You'll have to debug stepwise. What be the result of

sed  "s/\(JUN\|JUL\|AUG\)/XXXX/" file

?
And yes, try /usr/xpg4/bin/sed (NOT bib !).

duke0001 · October 30, 2019, 5:18pm

RudiC:

Thanks for new input. I tried and got this output,

/home/oracle/>/usr/xpg4/bin/sed "s/\(JUN\|JUL\|AUG\)/$NEWDATE" test4.txt
sed: command garbled: s/\(JUN\|JUL\|AUG\)/01-OCT-2019

Now the problem is that sed cannot find or match the month. If sed match the old date, then new date will replace it. I will continue to debug step by step. Thanks a lot.

RudiC · October 30, 2019, 6:30pm

You're missing a / at the end of your s command:

/home/oracle/>/usr/xpg4/bin/sed "s/\(JUN\|JUL\|AUG\)/$NEWDATE/" test4.txt

duke0001 · October 31, 2019, 10:01am

RudiC:

Yes. I missed a / and tested again. The result is still not working. It seems that sed cannot find MON in lines. This part- s/$JUN\|JUL\|AUG$ is not functioning. see output,

/home/oracle> /usr/xpg4/bin/sed "s/\(JUN\|JUL\|AUG\)/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

Thanks.

RudiC · October 31, 2019, 10:19am

Didn't you say you're doing a step by step debug?
Try

sed "s/\(JUL\)/XXX/" file

Read your sed version's man page!

duke0001 · October 31, 2019, 11:26am

RudiC:

Thanks. I debugged as this, this time, sed found the specific MON and replace it with whole value of $NEWDATE. But the DD and YYYY are not searched and replaced. I put DD in search, then the command doesn't work again. Here is output,

/home/oracle> /usr/xpg4/bin/sed "s/\(JUL\)/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-01-OCT-2019-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-01-OCT-2019-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-01-OCT-2019-2019'

/home/oracle> /usr/xpg4/bin/sed "s/[0-3][0-9]\(JUL\)/$NEWDATE/" test4.txt
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'
       ep.begin_date, ep.end_date, ep.facility_code,
AND    ep.begin_date <= '01-JUL-2019'

I will continue debug DD and YYYY
Thanks

duke0001 · October 31, 2019, 12:48pm

RudiC:

I have tried different ways. it didn't work. So I will take stupid way to do this work. I will pick up $OLDDATE and $NEWDATE two variables from database. Then:

/usr/xpg4/bin/sed "s/$OLDDATE/$NEWDATE/" test4.txt > test4_output.txt

It sounds working. Then I go to next step. Thanks anyway for your help and advice. I will continue to learn from this forum.