Use sed to prefix and suffix multiple strings per line

Question

I have a .sql file with some 15,000,000 INSERT commands on each line, which look something like this:

INSERT INTO tableName (col1,col2,col3,col4,col5,col6,col7,col8) VALUES ('Jan 7 2018 12:33PM', 'R', 'essential-app', '15952008002', 'Jan 7 2018 12:30PM', '0', 'E', NULL);

I want to wrap both date/time values so they look like STR_TO_DATE('Jan 7 2018 12:33PM',%b %d %Y %h:%i%p) - preserving the original value, of course.

I have used sed to make small changes to files before, but never something as complicated as this. How do I go about targeting both those date/time values, and how do I go about adding strings to both the beginning and end of the matched strings?

Add to the beginning: STR_TO_DATE(
Add to the end: ,%b %d %Y %h:%i%p)

Can it be done in one sed command, or do I need a sed command for each date/time value?

Answer 1

Try:

sed -r -e 's/'\''[A-Z][a-z][a-z]\s+[0-9]+\s[0-9]{4}\s+[0-9]+:[0-9][0-9][AP]M'\''/STR_TO_DATE(&, '\''%b  %d %Y %h:%i%p'\'')/g'

Notes:

• '\'' is how you insert a single-quote in a single-quoted string.

• The regexp matches:

  • a single-quote ', followed by
  • an uppercase letter and two lowercase letters Jan (or Feb etc.),
  • one or more spaces, one or more digits, a space, four digits 7 2018,
  • one or more spaces, one or more digits, a colon, two digits, an A or P, an M 12:30PM, and finally
  • another single-quote '.

This transforms

INSERT INTO tableName (col1,col2,col3,col4,col5,col6,col7,col8) VALUES ('Jan  7 2018 12:33PM', 'R', 'essential-app', '15952008002', 'Jan  7 2018 12:30PM', '0', 'E', NULL);

into

INSERT INTO tableName (col1,col2,col3,col4,col5,col6,col7,col8) VALUES (STR_TO_DATE('Jan  7 2018 12:33PM', '%b  %d %Y %h:%i%p'), 'R', 'essential-app', '15952008002', STR_TO_DATE('Jan  7 2018 12:30PM', '%b  %d %Y %h:%i%p'), '0', 'E', NULL);

I took the liberty of wrapping the format string in single-quotes. I hope this is what was intended.