TSQL – Extract Numbers from String

If you want to extract numbers all the numbers (8702000118) from the following string “BlaBla Hill is – 870-200-0118 abx” just use the code below:

DECLARE @YourString VARCHAR(256);
SET @YourString =’BlaBla Hill is – 870-200-0118 abx’

DECLARE @intAlpha INT
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @YourString)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @YourString = STUFF(@YourString, @intAlpha, 1, ” )
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @YourString )
END
END
SELECT ISNULL(@YourString,0)