SQL to JSON: Why Regex Cannot Reliably Parse INSERT

1. Problem: VALUES is not CSV

In INSERT INTO t VALUES ('a,b', 2), the comma belongs to a string. Batch VALUES adds multiple parenthesized groups. A regex that does not track quotes and escapes cannot tell a delimiter from data, and deleting characters is not a repair strategy.

2. Minimal reproduction: a lazy capture still stops inside data

This pattern is only a narrow demonstration and has no general guarantee for parentheses or escaped quotes.

const sql = "INSERT INTO logs(message) VALUES ('a,b (quoted)')";
const naive = /INSERT\s+INTO\s+.+?VALUES\s*\((.*?)\)/is;
console.log(naive.exec(sql)?.[1]);
// A closing parenthesis inside a value can be mistaken for the end.

3. Root cause: SQL has lexical state and dialects

Single-quoted strings can use doubled quotes or backslash escapes. MySQL has backtick identifiers, while PostgreSQL can use dollar-quoted strings. Comments, functions, and semicolons also affect boundaries. A single general regex cannot model all of these states safely.

4. Recommendation: declare a dialect and use a real parser

For a controlled, single-line INSERT subset, a limited scanner can fail explicitly on unsupported syntax. Production dumps, batch values, functions, comments, and mixed dialects need a parser that declares the target dialect and exposes a documented AST; do not invent a parser API.

5. Complete code: safe top-level statement splitting only

This browser- and Node-compatible TypeScript splits semicolons outside quotes and comments, and rejects PostgreSQL dollar quotes. It is not an INSERT-value parser; each statement still needs a dialect parser.

function splitSqlStatements(sql: string): string[] {
  const result: string[] = []; let start = 0;
  let mode: "normal" | "single" | "double" | "backtick" | "line" | "block" = "normal";
  for (let i = 0; i < sql.length; i += 1) {
    const ch = sql[i], next = sql[i + 1];
    if (mode === "line") { if (ch === "\n") mode = "normal"; continue; }
    if (mode === "block") { if (ch === "*" && next === "/") { mode = "normal"; i += 1; } continue; }
    if (mode === "single") { if (ch === "\\") { i += 1; continue; } if (ch === "'" && next === "'") { i += 1; continue; } if (ch === "'") mode = "normal"; continue; }
    if (mode === "double" || mode === "backtick") { const quote = mode === "double" ? '"' : String.fromCharCode(96); if (ch === "\\") { i += 1; continue; } if (ch === quote && next === quote) { i += 1; continue; } if (ch === quote) mode = "normal"; continue; }
    if (sql.slice(i).match(/^\$\$|^\$[A-Za-z_][A-Za-z0-9_]*\$/)) throw new Error("Dollar quote needs a PostgreSQL parser");
    if (ch === "-" && next === "-") { mode = "line"; i += 1; continue; }
    if (ch === "#") { mode = "line"; continue; }
    if (ch === "/" && next === "*") { mode = "block"; i += 1; continue; }
    if (ch === "'") { mode = "single"; continue; }
    if (ch === '"') { mode = "double"; continue; }
    if (ch === String.fromCharCode(96)) { mode = "backtick"; continue; }
    if (ch === ";") { const statement = sql.slice(start, i).trim(); if (statement) result.push(statement); start = i + 1; }
  }
  if (mode !== "normal") throw new Error("Unterminated quote or comment");
  const tail = sql.slice(start).trim(); if (tail) result.push(tail);
  return result;
}
console.log(splitSqlStatements("INSERT INTO t VALUES ('a; b'); -- next\nINSERT INTO t VALUES (2);"));

6. Incorrect approaches

split(',') breaks commas inside strings; split(');') breaks strings and comments; quoting every long number changes string content and dialect meaning. A regex that works for one sample does not make a dump parser, and failed rows must not silently become JSON.

7. Boundaries: escapes, comments, batches, and dialects

Test quotes, doubled quotes, backslashes, commas, parentheses, semicolons, line and block comments, batch VALUES, backticks, and PostgreSQL dollar quotes. Dialects differ; a scanner should reject uncertainty rather than guess.

8. Verification

First assert statement count and source preservation. Then use the declared dialect parser to inspect INSERT table, column count, value count, and NULL semantics. Compare complex strings with a database client or official tool instead of checking only whether the final JSON looks plausible.

9. FAQ

Can regex parse SQL at all? It can cover a documented restricted subset, but must fail at its boundary.

Why is splitting statements insufficient? INSERT columns and values still require lexical and dialect parsing.

Can comments simply be removed? Not blindly; string data and dialect constructs can contain comment-like text.

10. Summary

Whether a comma, parenthesis, or semicolon is syntax depends on lexical context. Use regex only for a constrained grammar; complex dumps need a declared dialect and a real parser. A custom splitter can be useful, but it must stop at quotes, comments, and unsupported dollar quotes.