MSSQL中如何快速获取字段中的数字

介绍

在MSSQL中，数据经常被存储在字符串中，我们常常需要从文本字段中检索出数字，在其他数据库中，可以使用正则表达式或类似函数来提取数字，但是在MSSQL中没有类似的函数或操作符。

解决方法

方法一：使用PATINDEX

在MSSQL中，可以使用PATINDEX函数与通配符来查找数字。

SELECT 

    column_name, 
    SUBSTRING(
       column_name, 
       PATINDEX('%[0-9]%', column_name), 
       PATINDEX('%[^0-9]%', SUBSTRING(column_name, PATINDEX('%[0-9]%', column_name), LEN(column_name))) 
    ) AS numeric_value 
FROM 
    table_name

上面的查询将返回包含原始列名和提取的数字的结果集。

在此示例中，关键是使用PATINDEX函数和通配符来查找数字的位置。然后，使用SUBSTRING函数来提取数字并返回结果。

在PATINDEX('%[0-9]%', column_name)中，语句中所使用的%通配符与LIKE语句中使用的相同，表示匹配任意字符。所以此语句将匹配任何包含数字的字符串，并返回第一个数字的位置。

因为数字可以出现在字符串的任何位置，所以必须使用另一个PATINDEX查询第一个不是数字的字符的位置。这是因为我们需要提取的数字是原始块中的连续数字，而可能存在其他字符（字符以及空格等特殊字符）会破坏数字字符的连续性。SUBSTRING中的第二个参数是从包含第一个数字的位置开始提取字符串的地方。

最后一个参数确定用于截取的字符串的长度。

方法二：使用PARSENAME

MSSQL提供了一个名为PARSENAME的函数，该函数用于解析具有点分隔符的字符串中每个子字符串，第一个子字符串示为1，依此类推。

可以使用PARSENAME函数提取数字，因为如果字符串有点，该函数将返回正确的值。参考以下示例:

SELECT PARSENAME(REPLACE(column_name, ',', '.'), 1) numeric_value

FROM table_name

在此示例中，函数用逗号替换点以确保它可以正常运行。REPLACE函数将字符串中的逗号替换为点，以确保PARSENAME函数可以解析每个值。

如果存在多个以逗号分隔的数字，则需要在PARSENAME内添加多个函数编号，类似于以下语句：

SELECT 

    PARSENAME(REPLACE(column_name, ',', '.'), 1) as numeric_value1, 
    PARSENAME(REPLACE(column_name, ',', '.'), 2) as numeric_value2, 
FROM 
    table_name

此示例将返回包含两个以逗号分隔的数字的结果集。

方法三：使用CAST或CONVERT函数

如果字符串中仅包含数字并且格式正确，则可以使用CAST或CONVERT函数将字符串转换为数字类型。

以下是一个示例：

SELECT CAST(column_name AS int) AS numeric_value

FROM table_name

在此示例中，将字符串转换为整数类型，因此可以省略CAST函数中的 AS int 提示。

SELECT CONVERT(int, column_name) AS numeric_value

FROM table_name

无论哪种函数都可以在MSSQL中转换字符串数字。但是请注意，如果字符串不仅包含数字，则将会引发错误。

总结

在MSSQL中，可以使用PATINDEX函数与通配符或PARSENAME函数从文本字符串中提取数字，也可以使用CAST或CONVERT函数将字符串转换为数字类型。选择方法取决于查询的需求以及要处理的数据类型。重要的是，需要了解如何从字符串中提取数字，以便能够正确处理数据。