Extract only numbers from column in SQL

Introduction

Sometime developers need to extract integer values from a given string. As there is no such default function or procedure to retrive only integers from a string, we need to script an user defined function.

User defined function

CREATE FUNCTION udf_extractInteger(@string  VARCHAR(2000))
     RETURNS VARCHAR(2000)
AS
 BEGIN
     DECLARE @count int
     DECLARE @intNumbers VARCHAR(1000)
     SET @count = 0
     SET @intNumbers = ''

     WHILE @count <=  LEN(@string)
     BEGIN 
         IF SUBSTRING(@string, @count, 1)>='0' and  SUBSTRING (@string, @count, 1) <='9'
              BEGIN
                 SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
              END
         SET @count = @count + 1
     END
     RETURN @intNumbers
END
 GO 

How to use this function

SELECT dbo.udf_extractInteger('hello 123 world456') As Output

Output