SQL Server提取字符串中的数字|极客教程

SQL Server提取字符串中的数字

1. 引言

在进行数据处理和分析时，我们经常会遇到需要从字符串中提取数字的需求。SQL Server是一款常用的关系数据库管理系统，在处理字符串时也提供了一些内置函数和方法。本文将详细介绍如何使用SQL Server提取字符串中的数字。

2. 使用PATINDEX函数

2.1 PATINDEX函数简介

PATINDEX函数用于查找某个模式在字符串中的位置，返回第一个匹配项的起始位置。我们可以利用这个函数来提取字符串中的数字。

2.2 提取整数和小数

以下是使用PATINDEX函数提取字符串中整数和小数的示例代码：

DECLARE @str VARCHAR(100) = 'ABC123.45XYZ'
SELECT SUBSTRING(@str, PATINDEX('%[0-9]%', @str), LEN(@str)) AS Number

上述代码中，@str是要处理的字符串，PATINDEX('%[0-9]%', @str)查找字符串中第一个数字的起始位置，SUBSTRING函数将该位置起始到字符串结尾之间的内容作为结果返回。

代码的输出为：123.45XYZ。可以看到，PATINDEX函数找到了字符串中第一个数字的位置，然后使用SUBSTRING函数提取了该数字以及其后的内容。

2.3 提取负数

如果需要提取负数，可以稍微修改上述代码：

DECLARE @str VARCHAR(100) = 'ABC-123.45XYZ'
SELECT SUBSTRING(@str, PATINDEX('%[-0-9]%', @str), LEN(@str)) AS Number

上述代码中，PATINDEX('%[-0-9]%', @str)查找字符串中第一个数字或负号的位置。代码的输出为：-123.45XYZ。

2.4 提取多个数字

如果需要提取字符串中的多个数字，可以使用循环结合PATINDEX函数来实现：

DECLARE @str VARCHAR(100) = 'ABC123.45XYZ678.90'
DECLARE @pos INT = 1
WHILE PATINDEX('%[0-9]%', @str) > 0
BEGIN
    SET @pos = PATINDEX('%[0-9]%', @str)
    PRINT SUBSTRING(@str, @pos, LEN(@str))
    SET @str = SUBSTRING(@str, @pos + 1, LEN(@str))
END

上述代码中，使用循环不断找到字符串中的第一个数字，并输出该数字。然后将字符串中的第一个数字及其前面的内容截断，继续处理剩余的字符串。代码的输出为：

123.45XYZ678.90
23.45XYZ678.90
3.45XYZ678.90
45XYZ678.90
5XYZ678.90
XYZ678.90
YX678.90
X678.90
678.90
78.90
8.90
90
0

可以看到，循环逐个提取了字符串中的数字。

3. 使用正则表达式

3.1 引入正则表达式

除了使用PATINDEX函数外，我们还可以使用正则表达式进行字符串匹配和提取。SQL Server 2016版本后新增了对正则表达式的支持，我们可以使用内置的LIKE运算符结合正则表达式来提取数字。

3.2 提取整数和小数

以下是使用正则表达式提取字符串中整数和小数的示例代码：

DECLARE @str VARCHAR(100) = 'ABC123.45XYZ'
SELECT SUBSTRING(@str, PATINDEX('%[0-9.]%', @str), LEN(@str)) AS Number
WHERE @str LIKE '%[0-9.]%'

上述代码中，LIKE '%[0-9.]%'判断字符串中是否包含数字或小数点，PATINDEX('%[0-9.]%', @str)查找字符串中第一个数字或小数点的位置，SUBSTRING函数将该位置起始到字符串结尾之间的内容作为结果返回。

代码的输出为：123.45XYZ。可以看到，通过正则表达式的匹配和提取，我们同样能够获取字符串中的数字。

3.3 提取负数

如果需要提取负数，可以稍微修改上述代码：

DECLARE @str VARCHAR(100) = 'ABC-123.45XYZ'
SELECT SUBSTRING(@str, PATINDEX('%[-0-9.]%', @str), LEN(@str)) AS Number
WHERE @str LIKE '%[-0-9.]%'

上述代码中，LIKE '%[-0-9.]%'判断字符串中是否包含数字、小数点或负号。代码的输出为：-123.45XYZ。

3.4 提取多个数字

使用正则表达式提取多个数字时，可以利用循环和CROSS APPLY来实现：

DECLARE @str VARCHAR(100) = 'ABC123.45XYZ678.90'
SELECT Number
FROM (VALUES (@str)) AS T(String)
CROSS APPLY (SELECT SUBSTRING(String, Number.Position, Number.Length) AS Number
             FROM (VALUES (PATINDEX('%[0-9.]%', String), LEN(String))) AS Number(Position, Length)
             WHERE String LIKE '%[0-9.]%') AS N

上述代码中，将字符串作为一个查询结果集的虚拟表(VALUES)，再通过CROSS APPLY将结果集中的每一条数据作为输入传递给后续操作。在CROSS APPLY中，通过PATINDEX函数和LEN函数获取每个数字在字符串中的起始位置和长度，并使用SUBSTRING函数提取数字。

代码的输出为：

123.45XYZ678.90
23.45XYZ678.90
3.45XYZ678.90
45XYZ678.90
5XYZ678.90
XYZ678.90
YX678.90
X678.90
678.90
78.90
8.90
90

可以看到，通过循环和CROSS APPLY，我们同样能够逐个提取字符串中的数字。

4. 总结

本文介绍了在SQL Server中提取字符串中的数字的方法。我们可以使用内置的PATINDEX函数进行位置匹配，通过SUBSTRING函数来提取字符串中的数字。另外，SQL Server 2016版本后还支持正则表达式的使用，我们可以通过LIKE运算符结合正则表达式来实现字符串的匹配和提取。无论是使用PATINDEX函数还是正则表达式，我们都能够灵活应用，满足不同场景下的需求。