SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Data (
    Id INT IDENTITY(1,1),
    Description NVARCHAR(100)
)

INSERT Data
VALUES
    ('Some 123.4 value'),
    ('No value here'),
    ('A paragraph number 1.2.3 is not a valid float'),
    ('Mutiple 12.3 values 34.5junk here 23.4'),
    ('A date 12/31/2024 is not a number')
5 rows affected

-- SQL Server 2023 or later: Select first value found.
SELECT D.*, C.Result
FROM Data D
OUTER APPLY (
    SELECT TOP 1 TRY_CAST(S.value AS FLOAT) AS Result
    FROM STRING_SPLIT(D.description, ' ', 1) S
    WHERE TRY_CAST(S.value AS FLOAT) IS NOT NULL
    ORDER BY S.ordinal
) C
ORDER BY D.Id


Id
Description
Result


1
Some 123.4 value
123.4

2
No value here
null

3
A paragraph number 1.2.3 is not a valid float
null

4
Mutiple 12.3 values 34.5junk here 23.4
12.3

5
A date 12/31/2024 is not a number
null


-- Older versions: Select the max value found.
SELECT D.*, C.Result
FROM Data D
CROSS APPLY (
    SELECT MAX(TRY_CAST(S.value AS FLOAT)) AS Result
    FROM STRING_SPLIT(D.description, ' ') S
) C
ORDER BY D.Id


Id
Description
Result


1
Some 123.4 value
123.4

2
No value here
null

3
A paragraph number 1.2.3 is not a valid float
null

4
Mutiple 12.3 values 34.5junk here 23.4
23.4

5
A date 12/31/2024 is not a number
null


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Combined (for side-by-side comparison)
SELECT D.*, C1.Result1, C2.Result2
FROM Data D
OUTER APPLY (
    SELECT TOP 1 TRY_CAST(S.value AS FLOAT) AS Result1
    FROM STRING_SPLIT(D.description, ' ', 1) S
    WHERE TRY_CAST(S.value AS FLOAT) IS NOT NULL
    ORDER BY S.ordinal
) C1
CROSS APPLY (
    SELECT MAX(TRY_CAST(S.value AS FLOAT)) AS Result2
    FROM STRING_SPLIT(D.description, ' ') S
) C2
ORDER BY D.Id


Id
Description
Result1
Result2


1
Some 123.4 value
123.4
123.4

2
No value here
null
null

3
A paragraph number 1.2.3 is not a valid float
null
null

4
Mutiple 12.3 values 34.5junk here 23.4
12.3
23.4

5
A date 12/31/2024 is not a number
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	Description	Result
1	Some 123.4 value	123.4
2	No value here	null
3	A paragraph number 1.2.3 is not a valid float	null
4	Mutiple 12.3 values 34.5junk here 23.4	12.3
5	A date 12/31/2024 is not a number	null