SQL Query to extract the numbers before a specific word

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

The numbers that should be output always appear before the word Test.

I would like the SQL to be compatible with Microsoft SSMS.

Answer 1

You need to create a Scalar Function like this (I did this in SQL Server)

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

    SET @stringToParse = REPLACE(REPLACE(@stringToParse, '(',' '), ')','')
    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

You use like this select dbo.GetNumberBeforeStringTest()

e.g. select dbo.GetNumberBeforeStringTest('Random Text 3 Random 568 Text 5.5 Test Random Text 345')

Result: 5.5