Hey there! Let's figure out how to write regex patterns that reliably grab the parts before and after the AS keyword in various SQL alias scenarios. I'll cover common cases with examples and break down each regex so you understand how it works.

Key Scenarios & Corresponding Regex #

I've tailored patterns for the most common SQL alias formats you mentioned:

1. Basic Alias Format (e.g., Alias AS label) #

For simple cases where both the source and alias are plain identifiers:

(?i)\s*([^\s,]+)\s+AS\s+([^\s,]+)

Explanation:

• (?i): Makes the match case-insensitive (works for AS, as, As, etc.)
• \s*: Matches any leading whitespace (0 or more characters)
• ([^\s,]+): First capture group – grabs the source identifier (any sequence of non-space, non-comma characters)
• \s+AS\s+: Matches the AS keyword surrounded by one or more spaces
• ([^\s,]+): Second capture group – grabs the alias label

2. Columns with Square Brackets (e.g., [ColumnA] AS label) #

To handle identifiers wrapped in square brackets (common in SQL Server):

(?i)\s*(\[[^\]]+\])\s+AS\s+([^\s,]+)

Explanation:

• (\[[^\]]+\]): First capture group – specifically matches text wrapped in square brackets (avoids matching closing brackets prematurely)
• The rest of the pattern works the same as the basic version above

3. Aliases with a Following FROM Clause (e.g., Alias AS label FROM XYZ) #

To ignore the FROM clause and everything after it:

(?i)\s*([^\s,]+|\[[^\]]+\])\s+AS\s+([^\s,]+)(?=\s+FROM|$)

Explanation:

• ([^\s,]+|\[[^\]]+\]): Combined capture group – matches either a plain identifier OR a bracketed column
• (?=\s+FROM|$): Positive lookahead – ensures we stop matching right before a FROM keyword or the end of the string, so we don't include any part of the FROM clause

Example Matches #

Let's test the universal pattern (scenario 3) against your examples:

• Input: CustomerID AS CID → Capture Group 1: CustomerID, Capture Group 2: CID
• Input: [ColumnA] AS UserName → Capture Group 1: [ColumnA], Capture Group 2: UserName
• Input: TotalAmount AS Total FROM Orders → Capture Group 1: TotalAmount, Capture Group 2: Total

Edge Case Consideration #

If your aliases include spaces or are quoted (e.g., FirstName AS 'User First Name'), you can adjust the second capture group to handle quoted strings:

(?i)\s*([^\s,]+|\[[^\]]+\])\s+AS\s+(['"]?[^\s,]+['"]?)

This will match aliases with single/double quotes, though note that it doesn't handle escaped quotes (e.g., 'User''s Name') – for that, you'd need a more complex pattern.

Important Notes #

• This regex is optimized for alias scenarios – it won't work for AS used in other contexts like CREATE TABLE AS SELECT ...
• SQL can get very complex (nested subqueries, aliases with special characters), so for production use, consider a dedicated SQL parser instead of regex if possible. Regex works well for simple to moderately complex cases, but parsers are more reliable for full SQL syntax.

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