View Thread
 Print Thread
Function to Strip none-numeric chars
admin
Handy for grabbing a number from a string or varchar value


CREATE FUNCTION udf_StripNonnumericChars
    (
      @OrigString VARCHAR(255)
    )
RETURNS VARCHAR(255)
AS
    BEGIN
      /*
      32 is a space
      48 to 57 are our numbers 0-9
      65 to 90 are the capital letters A-Z
      97 to 122 are the lowercase letters a-z
      */

        DECLARE @NewString VARCHAR(255)


        DECLARE @Len INT ,
            @Ctr AS INT ,
            @Ctr2 AS INT ,
            @Char AS VARCHAR(1)

        SELECT  @Len = LEN(@OrigString)
        SELECT  @NewString = ''

        SELECT  @Ctr2 = 1 ,
                @Ctr = 1
        WHILE @Ctr <= @Len
            BEGIN
                SELECT  @Char = SUBSTRING(@OrigString, @Ctr, 1)
                IF (ASCII(@Char) BETWEEN 48 AND 57) OR ASCII(@Char) = 46
                    BEGIN    
                        SELECT  @NewString = @NewString + @Char
                        SELECT  @Ctr2 = @Ctr2 + 1
                    END
                SELECT  @Ctr = @Ctr + 1
                CONTINUE
            END
        IF @Ctr2 = 1
            SELECT  @NewString = NULL


        RETURN @NewString

    END
GO

Jordon Pilling | Heavencore Administrator
 
Jump to Forum