You are here: Home > Technical Articles > MS SQL Server - Script for function get numeric

MS Sql Server - Script for get numeric function

SQL script for function get numeric. Returns only the numeric values in a string.

CREATE FUNCTION dbo.f_get_numeric(
        @string                varchar(7999)
) RETURNS varchar(7999)

BEGIN
   
    DECLARE @newstring varchar(7999)
    DECLARE @num int
    DECLARE @code int
   
    SELECT @newstring = ''
    SELECT @num = 1

    WHILE @num < LEN(@string)+1
    BEGIN
        SET @code = ASCII(SUBSTRING(@String, @Num, 1))
        --- ascii: 48-57 (numbers 0-9)
        --- ascii: 65-90 (uppercase letters A-Z)
        --- ascii: 97-122 (lowercase letters a-z)
        IF (@code between 48 and 57)
        BEGIN
            SET @newstring = @newstring + SUBSTRING(@string, @num, 1)
        END
       
        SET @num = @num + 1
    END
   
    RETURN @newstring
   
END
GO


Published On: 02 Aug 2009