Stored procedure used to filter out all data that does not include numbers. Useful for importing data into integer fields.
IF OBJECT_ID(N'NumbersOnly', N'FN') IS NOT NULL
DROP FUNCTION [dbo].[NumbersOnly]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[NumbersOnly]
(
-- Add the parameters for the function here
@instr varchar(50)
)
RETURNS nvarchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(50), @position int
SET @position = 1
SET @Result = ''
WHILE @position <= DATALENGTH(@instr)
BEGIN
IF ASCII(SUBSTRING(@instr, @position, 1)) BETWEEN 48 AND 57
SET @Result = @Result + SUBSTRING(@instr, @position, 1)
SET @position = @position + 1
END
RETURN @Result
END
GO
