SQL 提取字符串中的数字

<think>我们有一个任务：在SQL Server中提取字符串中的数字。 根据引用内容，我们可以使用PATINDEX函数和STUFF函数来逐步删除非数字字符，从而得到纯数字字符串。 此外，引用[4]提供了一个封装好的函数示例。 步骤说明： 1. 使用PATINDEX函数找到第一个非数字字符的位置。 2. 使用STUFF函数将该非数字字符删除（替换为空字符串）。 3. 循环执行上述过程，直到字符串中没有非数字字符。 4. 最后将剩下的字符串转换为数字（如果需要返回数字类型，则转换；如果返回字符串则不需要）。 我们也可以参考引用[4]中的函数，它返回一个bigint类型。但有时字符串中可能有多个数字，或者我们想保留所有数字字符（包括连续的），这个函数会删除所有非数字字符，将剩下的数字字符连成一个数字字符串。 注意：如果字符串中有多个连续的数字，这个函数会将其合并成一个数字。例如'abc123def456'会变成123456。 如果我们需要分别提取多个数字，或者提取第一个出现的数字，则方法不同。但根据问题描述，我们只要求提取字符串中的数字（即删除所有非数字字符），所以上述方法可行。 下面我们根据引用[4]中的函数进行说明和调整。 函数定义（返回bigint）： ```sql CREATE FUNCTION dbo.GetNoFromStr(@str NVARCHAR(100)) RETURNS BIGINT AS BEGIN WHILE PATINDEX('%[^0-9]%', @str) > 0 BEGIN SET @str = STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '') END RETURN CONVERT(BIGINT, @str) END ``` 但是，这个函数有一个问题：如果字符串中没有数字，那么循环结束后@str为空字符串，转换bigint会出错。因此，我们可以调整一下，先判断是否有数字，或者返回字符串类型，或者处理空字符串的情况。 另外，如果字符串中的数字超过bigint的范围，也会出错。所以，我们可以返回一个字符串类型，这样更通用。 修改后的函数（返回数字字符串）： ```sql CREATE FUNCTION dbo.GetNumbersFromString(@str NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN WHILE PATINDEX('%[^0-9]%', @str) > 0 BEGIN SET @str = STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '') END RETURN @str END ``` 这个函数将删除所有非数字字符，返回剩下的数字字符串。如果字符串中没有数字，则返回空字符串。 使用示例： ```sql SELECT dbo.GetNumbersFromString('abc123def456') AS Numbers; -- 返回 '123456' SELECT dbo.GetNumbersFromString('没有数字的字符串') AS Numbers; -- 返回 '' ``` 但是，有时候我们可能希望提取字符串中第一个连续的数字段（例如'abc123def456'，只提取123），那么我们可以稍微修改一下： 提取第一个连续数字段： 思路：使用PATINDEX找到第一个数字出现的位置，然后从该位置开始，使用PATINDEX找到下一个非数字的位置（或者使用SUBSTRING和PATINDEX组合），然后截取这段数字。 我们可以这样写： ```sql CREATE FUNCTION dbo.GetFirstNumber(@str NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @start INT, @end INT, @result NVARCHAR(MAX) -- 找到第一个数字出现的位置 SET @start = PATINDEX('%[0-9]%', @str) IF @start = 0 RETURN '' -- 没有数字 -- 截取从第一个数字开始到字符串结尾 SET @str = SUBSTRING(@str, @start, LEN(@str)-@start+1) -- 找到第一个非数字出现的位置（在剩余字符串中） SET @end = PATINDEX('%[^0-9]%', @str) IF @end = 0 SET @result = @str -- 剩余部分全是数字 ELSE SET @result = LEFT(@str, @end-1) RETURN @result END ``` 使用示例： ```sql SELECT dbo.GetFirstNumber('abc123def456') AS FirstNumber; -- 返回 '123' SELECT dbo.GetFirstNumber('123abc456') AS FirstNumber; -- 返回 '123' SELECT dbo.GetFirstNumber('abc456') AS FirstNumber; -- 返回 '456' SELECT dbo.GetFirstNumber('没有数字') AS FirstNumber; -- 返回 '' ``` 根据需求，用户可能需要提取所有的数字（合并成一个数字字符串）或者第一个连续数字。这里我们提供了两种方法。 因此，在回答用户时，我们需要明确用户的需求：是提取所有数字字符还是第一个连续的数字段？ 由于用户的问题没有明确，我们可以先提供第一种方法（提取所有数字字符），因为这是常见需求，并且引用[4]中也是这种方法。如果用户有特殊需求，我们再调整。 所以，我们给出一个完整的解决方案，包括创建函数和使用示例。 注意：在SQL Server中，用户自定义函数（UDF）可能会影响性能，特别是在大数据集上。如果只是偶尔使用，函数是可行的。如果对性能要求高，可以考虑在查询中直接使用循环或递归CTE，但通常函数更易于重用。 下面我们提供第一种方法的函数（提取所有数字字符）并给出使用示例。 步骤： 1. 创建函数，输入为字符串，输出为数字字符串（删除所有非数字字符后的字符串）。 2. 使用该函数。 代码： ```sql -- 创建函数 CREATE FUNCTION dbo.ExtractNumbers(@inputString NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN WHILE PATINDEX('%[^0-9]%', @inputString) > 0 BEGIN SET @inputString = STUFF(@inputString, PATINDEX('%[^0-9]%', @inputString), 1, '') END RETURN @inputString END GO -- 使用示例 SELECT dbo.ExtractNumbers('abc123def456') AS ExtractedNumbers; -- 返回 '123456' SELECT dbo.ExtractNumbers('订单号：A1001') AS ExtractedNumbers; -- 返回 '1001' SELECT dbo.ExtractNumbers('没有数字') AS ExtractedNumbers; -- 返回 '' ``` 如果用户需要的是提取第一个连续数字，则使用上面提供的第二个函数。 最后，我们根据用户的问题，提供两种选择，并说明它们的区别。 注意：如果字符串中有负数或小数，上述方法会忽略负号和小数点。如果需要处理负数或小数，则需要调整模式。例如，负数可能需要保留负号，小数需要保留小数点。但这样会复杂很多，因为需要判断负号和小数点的位置（例如，负号只能在最前面，小数点只能有一个）。由于用户问题没有提到，我们默认只提取数字字符（0-9）。 因此，我们回答用户： 在SQL Server中提取字符串中的数字，常用的方法是使用PATINDEX和STUFF函数循环删除非数字字符，从而得到纯数字字符串。我们提供了一个用户自定义函数来实现这个功能。如果您需要提取第一个连续的数字段，我们也可以提供另一种方法。 下面我们给出第一种方法的完整代码和示例。 相关问题： 1. 如何提取字符串中的第一个连续数字？ 2. 如何提取字符串中的负数或小数？ 3. 在SQL Server中，除了使用循环函数，还有没有其他方法提取字符串中的数字？</think>### SQL Server 提取字符串中数字的方法 在 SQL Server 中提取字符串中的数字，核心思路是**删除所有非数字字符**，保留纯数字内容。以下是两种常用方法： #### 方法一：使用 PATINDEX 和 STUFF 循环删除非数字字符（推荐） ```sql -- 创建函数 CREATE FUNCTION dbo.ExtractNumbers(@inputString NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN WHILE PATINDEX('%[^0-9]%', @inputString) > 0 BEGIN SET @inputString = STUFF( @inputString, PATINDEX('%[^0-9]%', @inputString), -- 找到非数字位置 1, -- 删除1个字符 '' -- 替换为空 ) END RETURN @inputString END GO -- 使用示例 SELECT dbo.ExtractNumbers('订单号：A1001') AS Numbers; -- 返回 '1001' SELECT dbo.ExtractNumbers('abc123def456') AS Numbers; -- 返回 '123456' ``` #### 方法二：直接使用查询语句（无需创建函数） ```sql DECLARE @str NVARCHAR(100) = 'Price: $99.99 USD'; WHILE PATINDEX('%[^0-9]%', @str) > 0 BEGIN SET @str = STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '') END SELECT @str AS ExtractedNumbers; -- 返回 '9999' ``` #### 关键函数说明： 1. **`PATINDEX('%[^0-9]%', string)`** - 返回字符串中**第一个非数字字符**的位置 - `%[^0-9]%` 是正则表达式模式，匹配非数字字符[^4] 2. **`STUFF(string, start, length, newString)`** - 从指定位置删除字符并插入新内容 - 本例中：`STUFF(str, pos, 1, '')` 表示删除1个非数字字符[^4] 3. **循环逻辑**： 重复查找并删除非数字字符，直到字符串中只包含数字[^1][^4] > ⚠️ 注意： > - 此方法会**合并所有数字**（如"a1b2c3" → "123"） > - 如需保留小数点或负号，需修改正则表达式模式 > - 空字符串处理：无数字时返回空字符串 #### 示例场景： | 原始字符串 | 提取结果 | |------------|----------| | 'Room 205' | '205' | | 'ID#007' | '007' | | '100kg' | '100' | | 'NoNumber' | '' | ---