从 SQL Server 中的文本中提取数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9629880/
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
Extract numbers from a text in SQL Server
提问by Thomas
i was searching script to extract number from text in sql server and i found this
我正在搜索脚本以从 sql server 中的文本中提取数字，我发现了这个
CREATE FUNCTION [dbo].[GetNumbersFromText](@String VARCHAR(2000))
RETURNS @Number TABLE (Number INT)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
--Find a numeric charactor
IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
--If the next charactor is not a numeric one, the current number ends, so add a separator
IF (SUBSTRING(@String,@Count+1,1) < '0'OR SUBSTRING(@String,@Count+1,1) > '9') AND SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + ','
END
SET @Count = @Count + 1
END
---Split string to give a table with the numbers in the text
INSERT INTO @Number
SELECT DISTINCT items FROM dbo.Split(@IntNumbers, ',')
return
END
and call it like
并称之为
SELECT Number FROM Dbo.[GetNumbersFromText]('Give me 120 this week and 50 next week')
it works fine but i need more short code. can i use patindex to extract number from text. please anyone share small & good logic to do so. thanks
它工作正常，但我需要更多的短代码。我可以使用 patindex 从文本中提取数字吗？请任何人分享小而好的逻辑来这样做。谢谢
采纳答案by Mikael Eriksson
This is a bit shorter. Turned it into Inline Table Function that uses a recursive CTE to find the numbers.
这个有点短。将其转换为使用递归 CTE 查找数字的内联表函数。
create function [dbo].[GetNumbersFromText](@String varchar(2000))
returns table as return
(
with C as
(
select cast(substring(S.Value, S1.Pos, S2.L) as int) as Number,
stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
from (select @String+' ') as S(Value)
cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
union all
select cast(substring(S.Value, S1.Pos, S2.L) as int),
stuff(S.Value, 1, S1.Pos + S2.L, '')
from C as S
cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
where patindex('%[0-9]%', S.Value) > 0
)
select Number
from C
)
If you expect to have more than 100 numbers in the string you need to call it with option (maxrecursion 0).
如果您希望字符串中有 100 个以上的数字，则需要使用option (maxrecursion 0).
declare @S varchar(max)
set @S = 'Give me 120 this week and 50 next week'
select number from GetNumbersFromText(@S) option (maxrecursion 0)
回答by Andriy M
@Vikram's basic idea is not bad, but their query would return all the numbers as a single item. The following function returns a table containing separate numbers as found in the source string:
@Vikram 的基本思想还不错，但他们的查询会将所有数字作为单个项目返回。以下函数返回一个包含在源字符串中找到的单独数字的表：
CREATE FUNCTION dbo.GetNumbersFromText (@String varchar(2000))
RETURNS TABLE
AS
RETURN (
WITH NumbersSplit AS (
SELECT
C = SUBSTRING(@String, number, 1),
i = number,
g = number - ROW_NUMBER() OVER (ORDER BY number)
FROM master..spt_values
WHERE type = 'P'
AND SUBSTRING(@String, number, 1) BETWEEN '0' AND '9'
),
NumbersAssembled AS (
SELECT
number = CAST(
(SELECT C + '' FROM NumbersSplit WHERE g = g.g ORDER BY i FOR XML PATH (''))
AS varchar(2000)
)
FROM NumbersSplit g
GROUP BY g
)
SELECT * FROM NumbersAssembled
)
Note: this solution would work in SQL Server 2005 or later.
注意：此解决方案适用于 SQL Server 2005 或更高版本。
回答by Vikram
try the following logic:
尝试以下逻辑：
declare @thestring varchar(50)
set @thestring = 'Give me 120 this week and 50 next week'
declare @final varchar(50)
set @final = ''
select @final = @final + x.thenum
from
(
select substring(@thestring, number, 1) as thenum, number
from master..spt_values
where substring(@thestring, number, 1) like '[0-9]' and type='P'
) x
order by x.number
print @final
回答by Mahdi Hassani Goodarzi
-- Try This Code... -- Its OK!!!
-- 试试这个代码... -- 没问题！！！
CREATE FUNCTION [dbo].[udf_ExtractNumberFromString]
(
@pInputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @OutputString varchar(MAX)=''
DECLARE @string varchar(MAX)
DECLARE @start INT
DECLARE @end INT
DECLARE @len INT
SET @string=@pInputString
--SET @string = 'the22478ddffafghrty12345TestAddressdd5aa789324-#345'
SET @string = replace(@string, ' ' , '')
WHILE PATINDEX('%[0-9]%',@string) <> 0
BEGIN
SET @len = len(@string)
-- PRINT @len
set @start = PATINDEX('%[0-9]%',@string)
-- PRINT @start
SET @end= PATINDEX('%[^0-9]%',SUBSTRING(@string,@start,@len-@start))
-- PRINT @end
IF @end=0
BEGIN
SET @end=@len-@start
SET @OutputString=SUBSTRING(@string,@start,@end+1)+'-'+@OutputString
BREAK
END
ELSE
BEGIN
SET @OutputString=SUBSTRING(@string,@start,@end-1)+'-'+@OutputString
SET @string=SUBSTRING(@string,@end+@start-1,@len-@end)
END
--PRINT @string
--PRINT @Output
--PRINT '---------------------'
END
IF LEN(@OutputString)>0
SET @OutputString=LEFT(@OutputString,LEN(@OutputString)-1)
--PRINT @OutputString
RETURN @OutputString
END
回答by Imtiyaz
Here is an appropriate and working solution for this query
这是此查询的适当且有效的解决方案
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
