SQL 如何使用 TSQL 从字符串中提取数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14700214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract numbers from a string using TSQL
提问by Rodricks
I have a string:
我有一个字符串：
@string='TEST RESULTS\TEST 1\RESULT 1
@string='TEST RESULTS\TEST 1\RESULT 1
The string/text remains the same except for the numbers
除数字外，字符串/文本保持不变
- need the 1 from TEST
- need 1 from RESULT
- 需要来自 TEST 的 1
- 需要 1 来自结果
to be used in a query like:
用于如下查询：
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)"
采纳答案by Tim Lehner
Since you have stable text and only 2 elements, you can make good use of replaceand parsename:
由于您有稳定的文本并且只有 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
*/
The replace portion does assume the same text and spacing always, and sets up for parsename with the period.
替换部分始终假定相同的文本和间距，并使用句点设置 parsename。
回答by gRUNGEbOB
Looks like you already have a solution that met your needs but I have a little trick that I use to extract numbers from strings that I thought might benefit someone. It takes advantage of the FOR XML statement and avoids explicit loops. It makes a good inline table function or simple scalar. Do with it what you will :)
看起来您已经有了一个满足您需求的解决方案，但我有一个小技巧可以用来从我认为可能对某人有益的字符串中提取数字。它利用 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
Result ==> 1234510
结果 ==> 1234510
回答by Imtiyaz
You can script an sql function which can used through your search queries. Here is the sample code.
您可以编写可通过搜索查询使用的 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
QUERY :
询问 ：
SELECT dbo.udf_extractInteger('hello 123 world456') As output
OUTPUT: 123456
输出：123456
Referred from : http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx
引用自：http: //www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx
回答by sgeddes
This method uses SUBSTRING, PARSENAME, and PATINDEX:
此方法使用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
Use PARSENAMEto split the string. The text of the string won't matter -- it will just need to contain the 2 back slashes to parse to 3 elements. Use PATINDEXwith a regular expression to replace non-numeric values from the result. This would need adjusting if the text in front of the number ever contained numbers.
使用PARSENAME拆分字符串。字符串的文本无关紧要——它只需要包含 2 个反斜杠即可解析为 3 个元素。PATINDEX与正则表达式一起使用以替换结果中的非数字值。如果数字前面的文本包含数字，则需要进行调整。
If needed, CAST/CONVERT the results to int or the appropriate data type.
如果需要，将结果 CAST/CONVERT 转换为 int 或适当的数据类型。
Here is some sample Fiddle.
这是一些示例Fiddle。
Good luck.
祝你好运。
