3

I am using

Regex: (?<!WHEN\s)EMP_ID

Query: SELECT EMP_ID, CASE WHEN EMP_ID > 115 THEN 'greater' WHEN EMP_ID < 115 THEN 'lower' END AS TEST

Matches: 1 match ( EMP_ID)

But if I add any spaces after WHEN in this query, then it will show 2 matches of 'EMP_ID', which is wrong. Or if I use the where condition in my query and use this column name then also it will give me 2 matches of the column name.

How to correct this regex or use a different method to solve this issue in C#?

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Walter
  • 133
  • 1
  • 10
  • Use `(?<!WHEN\s+)EMP_ID` – Wiktor Stribiżew Nov 21 '22 at 10:50
  • _"How to correct this regex or use a different method to solve this issue in C#?"_ - you can't: SQL `SELECT` clauses can contain arbitrary nested sub-expressions and queries, [it is not mathetically possible for a regex to handle](https://en.wikipedia.org/wiki/Chomsky_hierarchy) those situations. You will need to use a SQL parser library (Microsoft has one for T-SQL in SQL Server's`SMO` library which (I believe, but can't prove) is built from the same Antlr sources as SQL Server's own parser): https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.sqlparser.parser.parser – Dai Nov 21 '22 at 11:08

1 Answers1

2

You can use

(?<!WHEN\s+)EMP_ID

Or, with whole word matching:

\b(?<!\bWHEN\s+)EMP_ID\b

See the .NET regex demo.

Since .NET regex flavor allows unknown length patterns inside lookbehinds, the (?<!WHEN\s+) works and fails any location that is immediately preceded with WHEN + one or more whitespaces.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • @Dai You have different requirements from OP. OP's requirement is to match one occurrence of `EMP_ID` in the given string. Nowhere is it written `TEST` must be matched. – Wiktor Stribiżew Nov 21 '22 at 11:12
  • _"I want to get the column names in the SQL query"_ - they said "column names". – Dai Nov 21 '22 at 11:17
  • @Dai Read the question, it is about the issue where the regex cannot properly match a string not preceded with a word followed with any amount of whitespaces. Not about matching any column names. – Wiktor Stribiżew Nov 21 '22 at 11:18
  • 1
    Nice of you to remove the exact quote I copied from the OP's question... – Dai Nov 21 '22 at 11:30
  • 1
    @Dai Because it was not the point of the whole question. I just adjusted the title and problem statement to the problem described in the body of the question. – Wiktor Stribiżew Nov 21 '22 at 11:33
  • It's not for you to derive the author's intent, they haven't even posted any clarifying comments yet; it's bad form for you to unilaterally edit their post to remove content and context without their consent - you never even asked them. – Dai Nov 21 '22 at 11:36
  • There is nothing to clarify, the question is clear to me, and we must help people by editing the titles to match the real question body. – Wiktor Stribiżew Nov 21 '22 at 11:40
  • Thanks guys. I feel both of you are correct in some way. Still thanks for the help – Walter Nov 22 '22 at 09:18