如何构建正则表达式匹配SQL WHERE子句中的列名

Hey there! Since you specified your WHERE clauses follow a consistent structure (like WHERE area = 'testarea' AND description = 'testdescription' AND ...), here's a reliable regex solution tailored to that scenario:

Regex Pattern #

(?<=WHERE |AND )\w+(?= = )

Breakdown of the Pattern #

• (?<=WHERE |AND ): Positive lookbehind to target positions immediately after either WHERE or AND (spaces included to avoid accidental matches with similar strings)
• \w+: Matches one or more word characters (letters, numbers, underscores) — this covers standard, unquoted SQL column names
• (?= = ): Positive lookahead to ensure the matched word is directly followed by = (aligned with your query's consistent spacing)

Example Usage (Python) #

If you're working in Python, here's how to put this into practice:

import re

sql_query = "SELECT * FROM test WHERE area = 'testarea' AND description = 'testdescription' AND status = 'active'"
pattern = r"(?<=WHERE |AND )\w+(?= = )"
column_names = re.findall(pattern, sql_query)

print(column_names)
# Output: ['area', 'description', 'status']

Important Notes #

• This regex only works for your specified structured format: it relies on columns always being followed by = 'value' and separated by AND with consistent spacing.
• It won't handle edge cases like:
  • Column names with special characters or spaces (e.g., "user name")
  • Conditions using operators other than = (like >, <, LIKE)
  • Nested conditions with parentheses
  • Functions applied to columns (e.g., UPPER(area) = 'TESTAREA')

If you ever need to handle more complex WHERE clauses later, using a dedicated SQL parser (instead of regex) would be a far more robust approach — but for your current use case, this regex should do the trick!

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