如何使用TSQL从字符串中提取数字

Question

我有一个字符串：

@string='TEST RESULTS\TEST 1\RESULT 1

该字符串/文本保持不变，除了数字以外。

1. 需要从TEST中获取1。
2. 需要从RESULT中获取1。

以便在查询中使用：

SET @sql =  "SELECT *
            FROM   TABLE
            WHERE  test = (expression FOR CASE 1 resulting IN INT 1)
                   AND result = (expression FOR CASE 2 resulting IN INT 1)"

- Rodricks

Answer 1

看起来你已经有一个满足你需求的解决方案了，但我有一个小技巧，可以从字符串中提取数字，我认为这可能会对某些人有所帮助。它利用FOR XML语句并避免了显式循环。它可以成为良好的内联表函数或简单的标量函数。尽管随意使用 :)

DECLARE @String varchar(255) = 'This1 Is2 my3 Test4 For Number5 Extr@ct10n';


SELECT
    CAST(( 
        SELECT CASE --// skips alpha. make sure comparison is done on upper case
            WHEN ( ASCII(UPPER(SUBSTRING(@String, Number, 1))) BETWEEN 48 AND 57 )
            THEN SUBSTRING(@String, Number, 1)
            ELSE ''END
        FROM
        ( 
            SELECT TOP 255 --// east way to get a list of numbers
                                           --// change value as needed.
                ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS Number
             FROM master.sys.all_columns a
                CROSS JOIN master.sys.all_columns b 
        ) AS n
        WHERE Number <= LEN(@String)
        --// use xml path to pivot the results to a row
        FOR XML PATH('') ) AS varchar(255)) AS Result

结果 ==> 1234510

- gRUNGEbOB

---

需要一点时间让我完全理解这是如何工作的，但它在今晚帮了我很大的忙，我很感激！谢谢。 - John

Answer 2

您可以编写一个SQL函数，通过搜索查询来使用它。以下是示例代码。

CREATE FUNCTION udf_extractInteger(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
    AS

    BEGIN
        DECLARE @count int
        DECLARE @intNumbers VARCHAR(1000)
        SET @count = 0
        SET @intNumbers = ''

        WHILE @count <= LEN(@string)
        BEGIN 
            IF SUBSTRING(@string, @count, 1)>='0' and SUBSTRING (@string, @count, 1) <='9'
                BEGIN
                    SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
                END
            SET @count = @count + 1
        END
        RETURN @intNumbers
    END
    GO

查询：

SELECT dbo.udf_extractInteger('hello 123 world456') As output

输出：
123456

引用自：http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

- Imtiyaz

Answer 3

由于您拥有稳定的文本和仅有2个元素，您可以充分利用replace和parsename：

declare @string varchar(100) = 'TEST RESULTS\TEST 1\RESULT 2'

select cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 2) as int) as Test
    , cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 1) as int) as Result

/*
       Test      Result
----------- -----------
          1           2
*/

替换部分假定文本和间距始终相同，并设置为带有句点的parsename。

- Tim Lehner

---

谢谢Tim，我已经按预期获得了结果。 - Rodricks

Answer 4

此方法使用SUBSTRING、PARSENAME和PATINDEX：

SELECT 
  SUBSTRING(PARSENAME(c,2), PATINDEX('%[0-9]%',PARSENAME(c,2)), LEN(c)) Test,
  SUBSTRING(PARSENAME(c,1), PATINDEX('%[0-9]%',PARSENAME(c,1)), LEN(c)) Result
FROM (   SELECT REPLACE(@val, '\', '.') c) t

使用 PARSENAME 来拆分字符串。 字符串的文本内容并不重要 - 它只需要包含 2 个反斜杠以解析为 3 个元素。 使用正则表达式的 PATINDEX 将非数字值替换为结果。 如果数字前面的文本内容也包含数字，则需要进行调整。

如有必要，请将结果转换为 int 或适当的数据类型，使用 CAST/CONVERT。

下面是一些示例 Fiddle。

祝好运。

- sgeddes

---

感谢Sgeddes，非常感谢您解释表达式的工作原理。 - Rodricks

@Rodricks -- 没事，很高兴我们能帮忙。我喜欢这种方法，因为它不需要在代码中硬编码数值。祝你好运。 - sgeddes