使用正则表达式匹配模式

14 分钟

数据库中的文本处理通常需要模式匹配，而这超出了 LIKE 运算符的处理能力。正则表达式为复杂的模式匹配、验证和文本转换提供标准化语法。 Microsoft Fabric 中的 SQL Server 2025 和 SQL 数据库，通过新的 T-SQL 函数提供正则表达式支持。

请考虑 LIKE 不足的情形：例如，验证格式正确的电子邮件地址、提取电话号码而不考虑格式变化、查找遵循特定命名约定的产品代码，或检测如连续重复字符的模式。运算符 LIKE 仅支持简单的通配符（% 对于任何字符， _ 对于单个字符），不能表达这些复杂模式。

正则表达式通过提供丰富的模式语言来解决这些限制。使用正则表达式，可以匹配特定字符范围，要求精确的重复次数，使用交替（匹配这个或那个），并捕获匹配文本部分进行提取或替换。了解正则表达式语法后，可以跨许多编程语言和工具应用它—为 SQL Server 编写的模式同样适用于 Python、JavaScript 和命令行实用工具。

了解正则表达式基础知识

正则表达式（正则）使用模式语法来描述文本模式。在了解 SQL Server 函数之前，让我们查看以下常见的正则表达式组件：

图案	Description	示例匹配
`.`	任何单个字符	`a.c`匹配“abc”、“a1c”
`*`	前面字符出现零次或多次	`abc`匹配“ac”、“abc”、“abbc”*
`+`	上述一个或多个	`ab+c`匹配“abc”、“abbc”，而不是“ac”
`?`	前面字符出现零次或一次	`colou?r`匹配“color”、“colour”
`^`	字符串开头	`^Hello`匹配以“Hello”开头的字符串
`$`	字符串结尾	`world$`匹配以“world”结尾的字符串
`[abc]`	字符类	`[aeiou]` 匹配任何元音
`[^abc]`	否定类	`[^0-9]` 匹配非数字字符
`\d`	数字（0-9）	`\d{3}` 匹配三位数字
`\w`	Word字符	`\w+` 匹配单词字符
`{n}`	恰好出现 n 次	`\d{4}` 匹配恰好四位数字
`{n,m}`	介于 n 和 m 次出现之间	`\d{2,4}` 匹配 2 到 4 位数字

注释

SQL Server 的正则表达式函数使用 ECMAScript 标准正则表达式语法。这与 JavaScript 和其他许多编程语言中使用的语法相同，使模式可移植到技术之间。

使用 `REGEXP_LIKE` 匹配模式

REGEXP_LIKE 如果字符串与正则表达式模式匹配，则返回 1 （true），否则返回 0 （false）。在WHERE子句中使用此函数以根据复杂模式筛选行：

-- Find customers with email addresses from specific domains
SELECT CustomerID, FirstName, LastName, EmailAddress
FROM SalesLT.Customer
WHERE REGEXP_LIKE(EmailAddress, '@(contoso|adventure-works|fabrikam)\.com$') = 1;

验证数据格式：

-- Find valid US phone numbers (various formats)
SELECT CustomerID, Phone
FROM SalesLT.Customer
WHERE REGEXP_LIKE(Phone, '^\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}$') = 1;

-- Validate product numbers match expected format (XX-XXXX)
SELECT ProductID, ProductNumber, Name
FROM SalesLT.Product
WHERE REGEXP_LIKE(ProductNumber, '^[A-Z]{2}-[A-Z0-9]{4,6}$') = 1;

使用标志参数进行不区分大小写的匹配：

-- 'i' flag enables case-insensitive matching
SELECT Name
FROM SalesLT.Product
WHERE REGEXP_LIKE(Name, 'frame', 'i') = 1;

小窍门

使用 REGEXP_LIKE 进行验证和筛选。当你只需要知道模式是否存在时，它比提取子字符串更有效。

将文本替换为 `REGEXP_REPLACE`

REGEXP_REPLACE 查找模式的所有匹配项，并将其替换为指定的字符串。此函数可用于数据清理和标准化：

-- Standardize phone numbers to (XXX) XXX-XXXX format
SELECT 
    Phone AS OriginalPhone,
    REGEXP_REPLACE(
        REGEXP_REPLACE(Phone, '[^\d]', ''),  -- First remove all non-digits
        '^(\d{3})(\d{3})(\d{4})$',
        '($1) $2-$3'
    ) AS StandardizedPhone
FROM SalesLT.Customer
WHERE Phone IS NOT NULL;

以下示例演示如何将捕获组与反向引用配合使用：

-- Swap first and last name
DECLARE @name NVARCHAR(100) = 'Smith, John';
SELECT REGEXP_REPLACE(@name, '^(\w+),\s*(\w+)$', '$2 $1') AS SwappedName;
-- Returns: John Smith

-- Mask credit card numbers (show last 4 digits only)
DECLARE @card NVARCHAR(20) = '4532-1234-5678-9012';
SELECT REGEXP_REPLACE(@card, '\d(?=[\d-]{4,})', '*') AS MaskedCard;
-- Returns: ****-****-****-9012

以下示例演示如何清理和规范化数据：

-- Remove extra whitespace (multiple spaces to single space)
SELECT REGEXP_REPLACE(Description, '\s+', ' ') AS CleanedDescription
FROM Products;

-- Remove HTML tags
SELECT REGEXP_REPLACE(HtmlContent, '<[^>]+>', '') AS PlainText
FROM WebPages;

使用 REGEXP_SUBSTR 提取子字符串

REGEXP_SUBSTR 提取与正则表达式模式匹配的字符串部分。使用它从非结构化文本中提取特定数据元素，如以下示例所示：

-- Extract domain from email address
SELECT 
    EmailAddress,
    REGEXP_SUBSTR(EmailAddress, '@(.+)$', 1, 1, '', 1) AS Domain
FROM SalesLT.Customer
WHERE EmailAddress IS NOT NULL;

-- Extract the first number from a string
SELECT 
    ProductNumber,
    REGEXP_SUBSTR(ProductNumber, '\d+') AS FirstNumber
FROM SalesLT.Product;

以下示例演示函数签名，其中包括出现和捕获组的参数：

REGEXP_SUBSTR(source, pattern, start_position, occurrence, flags, capture_group)

使用 `REGEXP_INSTR` 查找模式位置

REGEXP_INSTR 返回字符串中模式匹配的起始位置。如果未找到匹配项，则返回 0，如以下示例所示：

-- Find position of first digit in product number
SELECT 
    ProductNumber,
    REGEXP_INSTR(ProductNumber, '\d') AS FirstDigitPosition
FROM SalesLT.Product;

-- Find position of email domain
SELECT 
    EmailAddress,
    REGEXP_INSTR(EmailAddress, '@') AS AtPosition,
    REGEXP_INSTR(EmailAddress, '\.[a-z]+$', 1, 1, 0, 'i') AS TldPosition
FROM SalesLT.Customer
WHERE EmailAddress IS NOT NULL;

使用 `REGEXP_COUNT` 统计模式出现次数

REGEXP_COUNT 返回模式出现在字符串中的次数。以下示例说明了其用法：

-- Count words in a description
SELECT  
    Name,
    REGEXP_COUNT(Name, '\w+') AS WordCount
FROM SalesLT.Product;

-- Count vowels in product names
SELECT 
    Name,
    REGEXP_COUNT(Name, '[aeiou]', 1, 'i') AS VowelCount
FROM SalesLT.Product;

-- Find products with multiple numbers in their name
SELECT Name
FROM SalesLT.Product
WHERE REGEXP_COUNT(Name, '\d+') > 1;

使用 `REGEXP_SPLIT_TO_TABLE` 拆分字符串

REGEXP_SPLIT_TO_TABLE 是一个基于分隔符模式将字符串拆分为行的表值函数：

-- Split comma-separated values
DECLARE @tags NVARCHAR(200) = 'sql,database,azure,analytics';
SELECT value AS Tag
FROM REGEXP_SPLIT_TO_TABLE(@tags, ',');

-- Split on multiple delimiters (comma, semicolon, or pipe)
DECLARE @data NVARCHAR(200) = 'apple,banana;cherry|date';
SELECT value AS Fruit
FROM REGEXP_SPLIT_TO_TABLE(@data, '[,;|]');

可以将REGEXP_SPLIT_TO_TABLE与其他查询一起使用CROSS APPLY。

-- Assuming Products table has a Tags column with comma-separated values
SELECT 
    p.ProductID,
    p.Name,
    t.value AS Tag
FROM Products AS p
CROSS APPLY REGEXP_SPLIT_TO_TABLE(p.Tags, ',\s*') AS t;

返回所有匹配项 `REGEXP_MATCHES`

REGEXP_MATCHES 是一个表值函数，它以单独的行的形式返回所有模式匹配项：

-- Find all numbers in a string
DECLARE @text NVARCHAR(200) = 'Order 12345 contains 3 items totaling $99.99';
SELECT match_value, match_index
FROM REGEXP_MATCHES(@text, '\d+\.?\d*');
-- Returns: 12345, 3, 99.99

重要

正则表达式函数适用于 SQL Server 2025 和 Microsoft Fabric 中的 SQL 数据库。对于早期 SQL Server 版本，可以考虑使用 CLR 函数或在应用程序层面处理复杂的正则表达式操作。

有关正则表达式函数的详细信息，请参阅正则表达式。

反馈

此页面是否有帮助？

使用正则表达式匹配模式

了解正则表达式基础知识

使用 REGEXP_LIKE 匹配模式

将文本替换为 REGEXP_REPLACE

使用 REGEXP_SUBSTR 提取子字符串

使用 REGEXP_INSTR 查找模式位置

使用 REGEXP_COUNT 统计模式出现次数

使用 REGEXP_SPLIT_TO_TABLE 拆分字符串

返回所有匹配项 REGEXP_MATCHES