从 SQL Server 中的文本中提取数字 [英] Extract numbers from a text in SQL Server
本文介绍了从 SQL Server 中的文本中提取数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在搜索脚本以从 sql server 中的文本中提取数字,我发现了这个
i was searching script to extract number from text in sql server and i found this
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
并称之为
SELECT Number FROM Dbo.[GetNumbersFromText]('Give me 120 this week and 50 next week')
它工作正常,但我需要更短的代码.我可以使用 patindex 从文本中提取数字吗?请任何人分享小&这样做的好逻辑.谢谢
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
推荐答案
这有点短.将其转换为使用递归 CTE 查找数字的内联表函数.
This is a bit shorter. Turned it into Inline Table Function that uses a recursive CTE to find the numbers.
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
)
如果您希望字符串中有超过 100 个数字,您需要使用 option (maxrecursion 0)
调用它.
If you expect to have more than 100 numbers in the string you need to call it with 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)
这篇关于从 SQL Server 中的文本中提取数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文