How to retrieve only numerical values from a string

What you want to do will not be achieved by some basic PATINDEX, you will need to run a loop for the string and get every numeric value out of it. Consider the following SQL function as an example.

CREATE FUNCTION NumbersFromString (@string VARCHAR(100))
RETURNS VARCHAR(100)
AS
   BEGIN
     DECLARE @loc INT
     --Get the location of the first letter in the string that is not a number.
     SET @loc = PATINDEX('%[^0-9]%', @string )
       BEGIN
         --Iterate till there is a non-numeric letter in the string.
         WHILE @loc > 0
           BEGIN
             --Replace the letter found at the location with ''.
             SET @string = STUFF(@string , @loc, 1, '' )
             --Set location to next letter.
             SET @loc = PATINDEX('%[^0-9]%', @string )
           END
       END
       --Return result.
       RETURN ISNULL(@string, 0)
    END;