0

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?

2

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.

| improve this answer | |
  • This works perfectly, thanks so much! And yes, you are correct about wrapping the format string in quotes as well - good catch! – tutigan Dec 5 '18 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.