MSSQL之非常规字符串转换成整数-猿码集

MSSQL之非常规字符串转换成整数

在MSSQL数据库开发过程中，有时我们会遇到将非数字字符串转换成整数的情况。在一般情况下，我们可以使用CAST或CONVERT函数将字符串转换为整数类型。但是，当字符串中包含非数字字符时，这些函数将无法正常转换。本文将介绍一些非常规字符串转换成整数的方法。

1. 使用REPLACE函数

当字符串中包含非数字字符时，我们可以使用REPLACE函数将这些字符替换成空白字符，然后再将结果转换为整数类型。


DECLARE @str VARCHAR(50) = '123ab4c';
SELECT CAST(REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1), '') AS INT)

运行以上代码，结果为127。

关键代码说明：

PATINDEX('%[^0-9]%', @str) 查找字符串中第一个非数字字符的位置。

SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1) 获取字符串中第一个非数字字符。

REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1), '') 将字符串中第一个非数字字符替换成空白字符。

CAST(... AS INT) 将结果转换为整数类型。

2. 使用STUFF函数

STUFF函数可以将一个字符串的一部分替换为另一个字符串。


DECLARE @str VARCHAR(50) = '123ab4c';
SELECT CAST(STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '') AS INT)

运行以上代码，结果为127。

关键代码说明：

PATINDEX('%[^0-9]%', @str) 查找字符串中第一个非数字字符的位置。

STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '') 将字符串中第一个非数字字符替换成空白字符。

CAST(... AS INT) 将结果转换为整数类型。

3. 使用CTE和RECURSIVE函数

CTE（Common Table Expression，公共表达式）可以帮助我们实现递归查询。在整数字符串的情况下，我们可以使用递归函数将字符串中的每个数字提取出来，并将它们拼接成整数字符串，再将其转换为整数类型。


DECLARE @str VARCHAR(50) = '1234ab5678c';
WITH tmp(txt, ovf, result) AS (
    SELECT LEFT(@str, PATINDEX('%[^0-9]%', @str + 'z') - 1),
           RIGHT(@str, LEN(@str) - PATINDEX('%[^0-9]%', @str + 'z') + 1),
           CAST(LEFT(@str, PATINDEX('%[^0-9]%', @str + 'z') - 1) AS BIGINT)
    UNION ALL
    SELECT LEFT(ovf, PATINDEX('%[^0-9]%', ovf + 'z') - 1),
           RIGHT(ovf, LEN(ovf) - PATINDEX('%[^0-9]%', ovf + 'z') + 1),
           result * 10 + CAST(LEFT(ovf, PATINDEX('%[^0-9]%', ovf + 'z') - 1) AS BIGINT)
    FROM tmp
    WHERE ovf LIKE '%[^0-9]%'
)
SELECT result FROM tmp
OPTION(MAXRECURSION 0)

运行以上代码，结果为12345678。

关键代码说明：

PATINDEX('%[^0-9]%', @str + 'z') 查找字符串中第一个非数字字符的位置，同时避免了字符串末尾没有非数字字符的情况。

LEFT(@str, PATINDEX('%[^0-9]%', @str + 'z') - 1) 获取字符串中的数字部分。

RIGHT(@str, LEN(@str) - PATINDEX('%[^0-9]%', @str + 'z') + 1) 获取字符串中的非数字部分。

CAST(LEFT(@str, PATINDEX('%[^0-9]%', @str + 'z') - 1) AS BIGINT) 将数字部分转换为整数类型。

WHERE ovf LIKE '%[^0-9]%' 终止递归。

OPTION(MAXRECURSION 0) 不限制递归次数。

本文介绍了三种将非常规字符串转换成整数的方法，分别是使用REPLACE函数、STUFF函数以及CTE和RECURSIVE函数。选择何种方法取决于具体情况，需要根据实际情况进行选择。

MSSQL之非常规字符串转换成整数