Get ALL unset parameters using regex in SQL query

user_2797782 0
Viewed 53
INSERT INTO TestTable
(
    Test1,
    Test2,
    Test3,
    Test4,
    Test5
)
VALUES
(
    @Test1,
    @Test2,
    @Test3,
    @Test4,
    @Test5
)

SET @Id = SCOPE_IDENTITY()

im trying to get all the unset variables in my query this is my Regex which also gets @Id which my goal is not to include all variable that has been set on the query

this is my regex

Regex.Matches(sql, @"\@\w+").Cast<Match>().Select(m => m.Value).ToList();
regex c#
1 Answers

You can use

\B@\w+\b(?!\s*=)

See the regex demo. Details:

  • \B@ - match @ not preceded with a word char (so as to avoid matching substrings like abc@def.com, etc.)
  • \w+ - one or more word chars
  • \b - word boundary (it is required to prevent backtracking when the subsequent negative lookahead fails the match, so that if the whole @word could be failed and so as not to return a @wor match)
  • (?!\s*=) - a negative lookahead that fails the match if, immediately to the right of the current location, there are zero or more whitespaces and then a = char.

Note that @ is not a special regex metacharacter and you do not need to escape it in the regular expression.

See the C# demo:

var text = "INSERT INTO TestTable\r\n(\r\n    Test1,\r\n    Test2,\r\n    Test3,\r\n    Test4,\r\n    Test5\r\n)\r\nVALUES\r\n(\r\n    @Test1,\r\n    @Test2,\r\n\r\n    @Test3,\r\n    @Test4,\r\n\r\n    @Test5\r\n)\r\n\r\nSET @Id = SCOPE_IDENTITY()";
var result = Regex.Matches(text, @"\B@\w+\b(?!\s*=)")
        .Cast<Match>()
        .Select(x => x.Value);
foreach (var s in result)
    Console.WriteLine(s);

Output:

@Test1
@Test2
@Test3
@Test4
@Test5
user_38329700
Related