How To Split Sql Insert Statement
“Don’t let yesterday take up too much of today.” - Will Rogers
Case study
Assume you have a SQL INSERT statement like this:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
If you want to split the values into a list, you can use the following code snippet:
import re
split_values = values.split(",")
If work in some case until for some values contains comma (,) inside single quotes (‘), like this:
INSERT INTO table_name VALUES (value1, value2, 'value3, value3.1', ...);
In this case, the split() method will not work as expected. To handle this case, you can use the following code snippet:
split_values = re.split(r",(?=(?:[^']*'[^']*')*[^']*$)", values)
Elaboration
Let’s break down the code snippet re.split(r",(?=(?:[^']*'[^']*')*[^']*$)", values):
The re.split() function is used to split a string based on a specified pattern.
In this case, the pattern is ",(?=(?:[^']*'[^']*')*[^']*$)".
Now, let’s analyze the pattern:
-
, - This matches a comma (,) character, which serves as the delimiter for splitting the string.
-
(?=(?:[^’]’[^’]’)[^’]$) - This is a positive lookahead assertion. It ensures that the comma (,) is followed by a specific pattern without actually consuming it during the split.
-
(?:[^’]’[^’]’)* - This non-capturing group (?: … ) matches zero or more occurrences of the following pattern: any character except a single quote ([^’]), followed by a single quote (‘), followed by any character except a single quote ([^’]), and another single quote (‘). Essentially, it matches pairs of single quotes and any characters between them.
-
[^’]$ - This matches any character except a single quote ([^’]) until the end of the string ($). It ensures that there are no unmatched single quotes after the comma.
Therefore, the overall pattern ",(?=(?:[^']*'[^']*')*[^']*$)" matches a comma (,) only if it is followed by an even number of single quotes (indicating it is not inside a pair of quotes).
The re.split() function splits the values string based on this pattern, effectively separating the values for each column while ignoring commas inside quotes. The resulting list, split_values, contains the extracted values.
Note that the strip(“’”) method is applied to each value in the for loop to remove the surrounding single quotes.
–HTH–
