如何使用TSQL从字符串中提取数字 [英] How to extract numbers from a string using TSQL

问题描述

我有一个字符串:

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

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

The string/text remains the same except for the numbers

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)"

推荐答案

由于文本稳定且只有两个元素，因此可以充分利用 parsename :

Since you have stable text and only 2 elements, you can make good use of replace and 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.

The replace portion does assume the same text and spacing always, and sets up for parsename with the period.

这篇关于如何使用TSQL从字符串中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！