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

Question

我有一个字符串:

@string='测试结果\测试 1\结果 1

除了数字之外，字符串/文本保持不变

1. 需要 TEST 中的 1
2. 需要结果中的 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)"

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