CREATE FUNCTION dbo.GetNumbers(@DATA VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN LEFT(
SUBSTRING(@DATA, PATINDEX(‘%[0-9.-]%’, @DATA), 8000),
PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@DATA, PATINDEX(‘%[0-9.-]%’, @DATA), 8000) + ‘X’)-1)
END

Example:
INSERT INTO @Temp VALUES(’2.1 miles’)
INSERT INTO @Temp VALUES(’4 miles’)
INSERT INTO @Temp VALUES(‘Approximately 6.5 miles’)
INSERT INTO @Temp VALUES(’3.9′)
INSERT INTO @Temp VALUES(’7.2miles’)
INSERT INTO @Temp VALUES(”)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(‘No Numbers Here’)
INSERT INTO @Temp VALUES(‘approximately 2.5 miles, but less than 3′)

SELECT DATA, dbo.GetNumbers(DATA)
FROM @Temp

OUTPUT:
Data Numbers
—————————————- ——-
2.1 miles 2.1
4 miles 4
Approximately 6.5 miles 6.5
3.9 3.9
7.2miles 7.2

NULL NULL
No Numbers Here
approximately 2.5 miles, but less than 3 2.5