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

Question

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

Answer 1

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