英文:
SQL Query to extract the numbers before a specific word, including floating point numbers
问题
String 4: '3.9'
英文:
I have data like this:
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'
The output I expect is:
String 1: '5.5'String 2: '3'String 3: Nothing should output
And this works using the below code, provided from here: https://stackoverflow.com/a/75923063/10787041
CREATE FUNCTION GetNumberBeforeStringTest(@stringToParse varchar(100))RETURNS VARCHAR(100)ASBEGINDECLARE @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 NULLRETURN NULL;RETURN @s2;ENDGO
I've noticed that when there is a string with brackets and a floating point the function doesn't work. An example would be:
String 4: 'Random Text (3.9 Test) Text Random'
Would it be possible to tell me how to edit the function to also be able to extract the number from a string that includes floating point numbers and a bracket, if this is having an affect on the function not working?
Thanks
答案1
得分: 2
请尝试以下解决方案。它将从SQL Server 2017开始起作用，因为使用了T-SQL的TRANSLATE()函数，无需任何UDF函数。
SQL
-- DDL and sample data population, startDECLARE @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'),('Random Text 777 Random Test'),('Random Text (3.9 Test) Text Random');-- DDL and sample data population, endDECLARE @separator CHAR(1) = SPACE(1);SELECT *, c.query('for $x in /root/rlet $pos := count(/root/r[. << $x]) + 1return if (xs:decimal($x) instance of xs:decimal (: filter out non-digits :)and /root/r[$pos + 1]/text()="Test") then $xelse ()').value('.','VARCHAR(MAX)') AS resultFROM @tbl AS tCROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +REPLACE(TRANSLATE(tokens,')(',' '), @separator, ']]></r><r><![CDATA[') +']]></r></root>' AS XML)) AS t1(c)ORDER BY ID;
Output
| ID | tokens | result |
|---|---|---|
| 1 | Random Text 3 Random 568 Text 5.5 Test Random Text 345 | 5.5 |
| 2 | Random Text 3 Test Text Random | 3 |
| 3 | Random Text 777 Random Text | |
| 4 | Random Text 777 Random Test | |
| 5 | Random Text (3.9 Test) Text Random | 3.9 |
英文:
Please try the following solution.
It will work starting from SQL Server 2017 onwards due to use of the T-SQL TRANSLATE() function
No need in any UDF function.
SQL
-- DDL and sample data population, startDECLARE @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'),('Random Text 777 Random Test'),('Random Text (3.9 Test) Text Random');-- DDL and sample data population, endDECLARE @separator CHAR(1) = SPACE(1);SELECT *, c.query('for $x in /root/rlet $pos := count(/root/r[. << $x]) + 1return if (xs:decimal($x) instance of xs:decimal (: filter out non-digits :)and /root/r[$pos + 1]/text()="Test") then $xelse ()').value('.','VARCHAR(MAX)') AS resultFROM @tbl AS tCROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +REPLACE(TRANSLATE(tokens,')(',SPACE(2)), @separator, ']]></r><r><![CDATA[') +']]></r></root>' AS XML)) AS t1(c)ORDER BY ID;
Output
| ID | tokens | result |
|---|---|---|
| 1 | Random Text 3 Random 568 Text 5.5 Test Random Text 345 | 5.5 |
| 2 | Random Text 3 Test Text Random | 3 |
| 3 | Random Text 777 Random Text | |
| 4 | Random Text 777 Random Test | |
| 5 | Random Text (3.9 Test) Text Random | 3.9 |
答案2
得分: 1
DECLARE @s1 VARCHAR(100) = REVERSE(REPLACE(REPLACE(TRIM(SUBSTRING(@stringToParse, 0, @testIndex)),'(',''),')',''))
英文:
You could remove any brackets within the string using the REPLACE function, the S1 line would look like this instead:
DECLARE @s1 VARCHAR(100) = REVERSE(REPLACE(REPLACE(TRIM(SUBSTRING(@stringToParse, 0, @testIndex)),'(',''),')',''))
答案3
得分: 1
请查看这个基于不同前提的替代内联表值函数。
它返回一个表，在处理一组行时性能会更好。
函数：
create or alter function dbo.GetNumberBeforeStringTest(@str varchar(100))returns table asreturn select [value] = Iif(Try_Cast(n.v as decimal) is null, null, n.v)from (select @str s)tcross apply(values(NullIf(CharIndex('test', t.s), 0)-1))test(p)cross apply(values(left(t.s, test.p)))l(p)cross apply(values(patindex('%[^0-9.]%', Trim(Reverse(l.p)))))x(p)cross apply(values(Right(l.p, x.p)))n(v);
使用方式：
select t.col, v.[value]from tcross apply dbo.GetNumberBeforeStringTest(col)v;
以及 a Fiddle demo。
英文:
Please have a look at this alternative inline-table-valued function that works on a different premise.
It returns a table which will be considerably more performant when used for a set of rows.
The function:
create or alter function dbo.GetNumberBeforeStringTest(@str varchar(100))returns table asreturn select [value] = Iif(Try_Cast(n.v as decimal) is null, null, n.v)from (select @str s)tcross apply(values(NullIf(CharIndex('test', t.s), 0)-1))test(p)cross apply(values(left(t.s, test.p)))l(p)cross apply(values(patindex('%[^0-9.]%', Trim(Reverse(l.p)))))x(p)cross apply(values(Right(l.p, x.p)))n(v);
And to use:
select t.col, v.[value]from tcross apply dbo.GetNumberBeforeStringTest(col)v;
And a Fiddle demo
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论