SQL Server Portal

Few days earlier, I was working on a legacy application data and found that some characters were merged with the numbers in a column of a table and the issue was, I had to get only the numbers from this column.
Given below are the samples which were available in the column.
129 mph
550 kmp

Let me create a sample to demonstrate the problem and its solution.

USE tempdb
GO
--Create a table
DECLARE @tbl_sample TABLE
(
ID INT,
Varchar_col VARCHAR(10)
)
--Insert sample records in the table
INSERT INTO @tbl_sample VALUES (1,'12.9 mph')
INSERT INTO @tbl_sample VALUES (2,NULL)
INSERT INTO @tbl_sample VALUES (3,'45')
INSERT INTO @tbl_sample VALUES (4,'90 mph')

--Browse the table
SELECT * FROM @tbl_sample

SOLUTION :
Given below is the solution using string manipulation function.

SELECT
ID
,Varchar_col
,LEFT(Varchar_col
,DATALENGTH(Varchar_col)-
(PATINDEX('%[0-9]%',REVERSE(Varchar_col))-1)) AS [Number]
FROM @tbl_sample

Let me know if you came across this situation and resolved it with the different solution.