4.11.08 MSSQL Functions To Strip Non-AlphaNumeric and Non-Numeric Characters In Character Fields

Thanks to Dan Venor and Scott Fitzpatrick and a little help isolating the ASCII Character values here are a two functions for use with inline MSSQL queries to strip non-alphanumeric and non-numeric characters in character fields respectively. This comes in handy if you’re using older versions of mssql that do not have regex (regular expression) syntax built in.

RemoveNonAlphaNumeric

ALTER  FUNCTION [dbo].[RemoveNonAlphaNumeric_fn] (@string VARCHAR(255))  
/* Function coded by Dan Venor @ MMSA - Original idea for non-numeric Scott Fitzgerald @ MMSA */
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @charstrip AS varchar(30)
    DECLARE @iCol AS integer
    SET @iCol = 0
    SET @charstrip = ''
    WHILE @iCol < len(@string)
        BEGIN
                    SET @iCol = @iCol + 1
                    BEGIN
                                IF  (ASCII(substring(@string, @iCol, 1)) >= 48 AND ASCII(substring(@string, @iCol, 1)) <= 57) OR
                        (ASCII(substring(@string, @iCol, 1)) >= 65 AND ASCII(substring(@string, @iCol, 1)) <= 90) OR
                        (ASCII(substring(@string, @iCol, 1)) >= 97 AND ASCII(substring(@string, @iCol, 1)) <= 122)
                        SET @charstrip = @charstrip + substring(@string, @iCol, 1) + ''
                    END
        END
    RETURN @charstrip
END

Download this code: mssql_RemoveNonAlphaNumeric_fn.sql

RemoveNonNumeric

ALTER  FUNCTION [dbo].[RemoveNonNumeric] (@string VARCHAR(255))  
/* Function coded by Dan Venor @ MMSA - Original idea and implementation for non-numeric Scott Fitzpatrick @ MMSA */
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @charstrip AS varchar(30)
    DECLARE @iCol AS integer
    SET @iCol = 0
    SET @charstrip = ''
    WHILE @iCol < len(@string)
        BEGIN
                    SET @iCol = @iCol + 1
                    BEGIN
                                IF  ASCII(substring(@string, @iCol, 1)) >= 48 AND ASCII(substring(@string, @iCol, 1)) <= 57
                        SET @charstrip = @charstrip + substring(@string, @iCol, 1) + ''
                    END
        END
    RETURN @charstrip
END

Download this code: mssql_RemoveNonNumeric_fn.sql

To use either, simply add as a function to the appropriate database which might involve bribing your DBA. Then use with inline SQL such as:
SELECT database.dbo.RemoveNonAlphaNumeric_fn('(555)444-1212')
or
SELECT database.dbo.RemoveNonAlphaNumeric_fn(CONVERT(VARCHAR(255), numericField)) FROM table WHERE table.index = 1

No comments




Preview your comment
0.446s