SQL Query to extract the numbers before a specific word, including floating point numbers

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)
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

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

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, 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'),
('Random Text 777 Random Test'),
('Random Text (3.9 Test) Text Random');
-- 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(TRANSLATE(tokens,')(',SPACE(2)), @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

Output

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)),'(',''),')',''))

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 as
return select [value] = Iif(Try_Cast(n.v as decimal) is null, null, n.v)
from (select @str s)t
cross 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 t
cross apply dbo.GetNumberBeforeStringTest(col)v;

And a Fiddle demo