Following SQL User Defined Function will extract/parse numbers from the string.
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
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
Run following script in query analyzer.
SELECT dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL')
GO
It will return following values.
3323111
Reference : Pinal Dave (https://blog.sqlauthority.com)
