Say I have this (with the format I show, no newlines)

INSERT INTO `somthing` (`something`) VALUES (1) INSERT INTO `somthing` (`somethingelse`) VALUES ('something with a (paranthesis) in it') INSERT INTO `somthing` (`something`) VALUES (3) INSERT INTO `somthing` (`something`) VALUES (4)

I want the output to be

INSERT INTO `somthing` (`something`) VALUES (1); INSERT INTO `somthing` (`somethingelse`) VALUES ('something with a (paranthesis) in it'); INSERT INTO `somthing` (`something`) VALUES (3); INSERT INTO `somthing` (`something`) VALUES (4);

So that they are legitimate SQL queries. I have tried this in sed:

sed 's/\(VALUES ([^)]*)\)/\1;/g')

Which works, except when there are parantheses inside the values, I am not sure what do to to fix that. Basically, I want add a semicolon to the end of (.*), (the last )) if it says VALUES before it.

  • To make your sample input useful, please include a part for which the sed code fails. And, of course, include the corresponding sample output for that case as well.
    – John1024
    Sep 5 '16 at 0:30
  • @John1024 Ok, did that now.
    – Josef Djur
    Sep 5 '16 at 0:33

There's no simple pattern that will be correct in every circumstance; you would need to write a SQL parser to determine when the end of statement should be... and even then you'd need to hope there's no special magic happening. Now ANSI SQL isn't Turing Complete (although extensions may be) so you probably could write a parser...

Or you could write a stack-based parser to add a ; after a closing )... and handle bad syntax.

Instead you might want to use something more likely to fail to match erronously. For example, this code picks up on the INSERT INTO statement and puts a ; in front of that, remembering to add one at the very end.

sed -e 's/\( INSERT INTO `somthing` (`something`) VALUES (\)/;\1/g' -e 's/$/;/'

Obviously it would fail if the inserted data had that specific string...


You can solve the problem by matching a different part of the statement, e.g.,

sed -e 's/\(VALUES (`[^`]*`)\)/\1;/g' \
    -e "s/\(VALUES ('[^']*')\)/\1;/g" \
    -e 's/\(VALUES ([0-9]*)\)/\1;/g'

That will not depend on what is inside the parentheses.

  • 1
    The .* will be greedy and match too far. Other than that, we had the same idea :-) Sep 5 '16 at 0:38
  • He does; that's the problem. The example provided has 4 INSERT statements on one line with no obvious delineation between them. Sep 5 '16 at 0:42
  • I think this final version passes the tests :-) That was hard work! Sep 5 '16 at 0:54
  • Oh, except for a final ; on the last statement, which is easy to add :-) Sep 5 '16 at 0:59
  • you can use \0 or & and avoid the use of \( \)
    – Sundeep
    Sep 5 '16 at 3:16

1) Assuming that the ending ) of VALUES will be followed by INSERT keyword or end of line, as given in sample input provided in question

perl -pe 's/VALUES\s*\(.*?\)(?=\s*INSERT|$)/$&;/g' file

2) Assuming there are two ways VALUES can be present:

  • VALUES without inner () won't have ' in them
  • VALUES with inner () will start as (' and end with ')

perl -pe "s/VALUES\s*\([^')]*\)/$&;/g ; s/VALUES\s*\('.*?'\)/$&;/g" file

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.