如何用正则将SQL INSERT转为键值对字典？C#正则匹配失败求助

Let's break down why your current regex isn't working, then fix it to capture all columns and values correctly for your use case.

What's Wrong With Your Original Regex? #

You've hit three main issues that prevent matching and proper capture:

1. Incorrect Named Group Syntax
   C# requires named capture groups to follow the (?<GroupName>pattern) format. Your regex uses (?[A-Z_]*) and (?[\""A-Za-z_]+) which are missing the <> around the group name—this makes the regex treat those as literal characters instead of capture groups.

2. Overwritten Captures from Repeating Groups
   Using ((?<Column>...),?)* means only the last column (and last value) will be captured. Repeating a group like this overwrites previous matches, so you can't get all columns/values this way.

3. Incomplete Pattern for Columns and Values

   • Your column pattern doesn't handle quoted identifiers like \"\"B\"\" properly—it only matches individual characters instead of the full quoted string.
   • The value pattern [^']+ doesn't account for escaped quotes (though your example doesn't have them, it's a good practice to support) or whitespace around commas in the VALUES clause.

Corrected Regex for C# #

Here's a regex that fixes all these issues, handles quoted columns/values, escaped characters, and captures the table name, full column list, and full value list:

var insertRegex = @"INSERT INTO (?<TableName>[A-Z_]+)\s*\(\s*(?<ColumnList>(?:[A-Za-z_]+|""(?:[^""\\]|\\.)*"")\s*(?:,\s*(?:[A-Za-z_]+|""(?:[^""\\]|\\.)*""))*)\s*\)\s*VALUES\s*\(\s*(?<ValueList>(?:'(?:[^'\\]|\\.)*'|\s*)\s*(?:,\s*(?:'(?:[^'\\]|\\.)*'|\s*))*)\s*\)";

Let's Break Down the Pattern: #

• (?<TableName>[A-Z_]+): Captures the target table name (matches uppercase letters and underscores).
• (?<ColumnList>...): Captures the entire comma-separated list of columns, supporting both:
  • Plain identifiers like A or C
  • Quoted identifiers like \"\"B\"\" (handles escaped double quotes inside)
• (?<ValueList>...): Captures the entire comma-separated list of values, including:
  • Single-quoted values (like your complex formula string)
  • Empty values ('')
  • Escaped single quotes (if present in your data)

How to Extract the Key-Value Dictionary #

Since we can't capture individual columns/values directly with a single repeating group, we'll split the captured column and value lists into individual items, then map them to a dictionary:

var inputString = @"INSERT INTO TABLEA(A,\""B\"",C,D,E,F,G,H,I,J,K,L,M) VALUES('RIC_MIGRATION','RIC_MIGRATION','AND ( ( IN ( CurrencyStr , \"\"AUD\"\",\"\"NZD\"\",\"\"EUR\"\" ) ) , AND ( 1, AND ( 1, AND ( ( FeedToPXE2 = \"\"1\"\") , ( RIC_CRED_ValueSource = \"\"1\"\") , ( FeedToPXE1 = \"\"0\"\") , ( IN ( InsType , \"\"A\"\",\"\"C\"\",\"\"T\"\" ) ) , )) , ) , )','','','EUR.CM_INSTRUMENT.REFDATA.INSTRUMENTGROUP_PXECRED_RIC_MIGRATION','1','1','0','','','0','No')";
var insertRegex = @"INSERT INTO (?<TableName>[A-Z_]+)\s*\(\s*(?<ColumnList>(?:[A-Za-z_]+|""(?:[^""\\]|\\.)*"")\s*(?:,\s*(?:[A-Za-z_]+|""(?:[^""\\]|\\.)*""))*)\s*\)\s*VALUES\s*\(\s*(?<ValueList>(?:'(?:[^'\\]|\\.)*'|\s*)\s*(?:,\s*(?:'(?:[^'\\]|\\.)*'|\s*))*)\s*\)";

var match = Regex.Match(inputString, insertRegex);
if (match.Success)
{
    // Parse columns from the captured list
    var columnMatches = Regex.Matches(match.Groups["ColumnList"].Value, @"(?:[A-Za-z_]+|""(?:[^""\\]|\\.)*"")");
    var columns = columnMatches.Cast<Match>()
        .Select(m => Regex.Unescape(m.Value.Trim('"'))) // Remove quotes and unescape characters
        .ToList();

    // Parse values from the captured list
    var valueMatches = Regex.Matches(match.Groups["ValueList"].Value, @"'(?:[^'\\]|\\.)*'");
    var values = valueMatches.Cast<Match>()
        .Select(m => Regex.Unescape(m.Value.Trim('\''))) // Remove quotes and unescape characters
        .ToList();

    // Create the key-value dictionary
    var sqlDict = columns.Zip(values, (col, val) => new { Column = col, Value = val })
                         .ToDictionary(pair => pair.Column, pair => pair.Value);

    // Test output
    foreach (var kvp in sqlDict)
    {
        Console.WriteLine($"{kvp.Key} -> {kvp.Value}");
    }
}

Key Notes on the Code: #

• We use sub-regexes to split the captured column and value lists into individual items—this avoids the "overwritten capture" problem from repeating groups.
• Regex.Unescape handles restoring escaped characters (like turning \"\" back into ").
• Zip pairs each column with its corresponding value, which we then convert to a dictionary.

A Word of Caution #

Regex works well for simple INSERT statements like your example, but it has limits. If you need to handle more complex SQL (multi-line statements, comments, bulk inserts, or non-standard syntax), consider using a dedicated SQL parsing library like Microsoft.SqlServer.TransactSql.ScriptDom—it's designed to properly parse SQL syntax without the edge cases that trip up regex.

内容的提问来源于stack exchange，提问作者radianz