SQL查询，提取特定单词前的数字

Question

我有这样的数据:

String 1: 'Random Text 3 Random 568 Text 5.5 Test Random Text 345'
String 2: 'Random Text 3 Test Text Random'
String 3: 'Random Text 777 Random Text'

我期望的输出是:

String 1: '5.5'
String 2: '3'
String 3: Nothing should output

应该输出的数字总是出现在单词Test之前。

我希望SQL与Microsoft SSMS兼容。

Answer 1

你需要像这样创建一个标量函数(我在SQL Server中这样做)

CREATE FUNCTION GetNumberBeforeStringTest
(
@stringToParse varchar(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @testIndex INT = PATINDEX('%test%', @stringToParse);

IF @testIndex = 0 RETURN NULL;
DECLARE @s1 VARCHAR(100) = REVERSE(TRIM(SUBSTRING(@stringToParse, 0, @testIndex)))

DECLARE @s2 VARCHAR(100) = REVERSE(TRIM(SUBSTRING(@s1, 0, CHARINDEX(' ', @s1))))
IF TRY_CAST(@s2 AS decimal) IS NULL
RETURN NULL;
RETURN @s2;
END
GO

你这样用选择dbo.GetNumberBeforeStringTest ()

。选择dbo。GetNumberBeforeStringTest ('随机文本3随机568文本5.5测试随机文本345')

结果:5.5

Answer 2

请尝试以下解决方案。

它通过XML和XQuery使用标记化。

值得注意的点:

• CROSS APPLY子句为每一行创建XML。
• XQuery FLWOR表达式正在检查后面跟着的数值"测试"字。
$pos变量保存每个单词的位置。

ID=1的XML示例

<root>
<r>Random</r>
<r>Text</r>
<r>3</r>
<r>Random</r>
<r>568</r>
<r>Text</r>
<r>5.5</r>
<r>Test</r>
<r>Random</r>
<r>Text</r>
<r>345</r>
</root>

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('Random Text 3 Random 568 Text 5.5 Test Random Text 345'),
('Random Text 3 Test Text Random'),
('Random Text 777 Random Text');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT *
, c.query('for $x in /root/r
let $pos := count(/root/r[. << $x]) + 1
return if (xs:decimal($x) instance of xs:decimal (: filter out non-digits :)
and /root/r[$pos+1]/text()="Test") then $x
else ()').value('.','VARCHAR(MAX)') AS result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

输出

<表类>ID标记结果1随机文本3随机568文本5.5测试随机文本3455.52随机文本3随机文本33随机文本777个随机文本

只是另一个使用JSON和窗口函数lead() over()的选项

DECLARE @YourTable Table (SomeCol VARCHAR(150));
INSERT @YourTable VALUES
('Random Text 3 Random 568 Text 5.5 Test Random Text 345'),
('Random Text 3 Test Text Random'),
('Random Text 777 Random Text');
Select A.SomeCol
,B.RetVal
From  @YourTable A
Outer Apply (
Select RetVal
From  (
Select RetVal = Value
,NxtVal = lead(Value,1) over (order by [Key])
From  OpenJSON( '["'+replace(string_escape(SomeCol,'json'),' ','","')+'"]' )
) B1
Where NxtVal='Test'
and try_convert(money,RetVal) is not null
) B

结果

SomeCol                                                   RetVal
Random Text 3 Random 568 Text 5.5 Test Random Text 345    5.5
Random Text 3 Test Text Random                            3
Random Text 777 Random Text                               NULL

假设模式(在'test'之前的数字)在所有字符串中是一致的，下面是提取数字的sql:

SELECT 
CASE 
WHEN CHARINDEX(' Test ', [String 1]) > 0 
THEN SUBSTRING([String 1], CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))+1, CHARINDEX(' ', [String 1], CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))+1) - CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))-1)
ELSE ''
END AS [String 1],
CASE 
WHEN CHARINDEX(' Test ', [String 2]) > 0 
THEN SUBSTRING([String 2], CHARINDEX(' ', [String 2])+1, CHARINDEX(' ', [String 2], CHARINDEX(' Test ', [String 2])) - CHARINDEX(' ', [String 2])-1)
ELSE ''
END AS [String 2],
CASE 
WHEN CHARINDEX(' Test ', [String 3]) > 0 
THEN SUBSTRING([String 3], CHARINDEX(' ', [String 3])+1, CHARINDEX(' ', [String 3], CHARINDEX(' Test ', [String 3])) - CHARINDEX(' ', [String 3])-1)
ELSE ''
END AS [String 3]

希望有帮助!