Stripping all Non Alphanumeric Characters From String in SQL

With one of the projects I’m currently working on using Taskcentre, I required all non alphanumeric characters stripping from a string in a SQL query (in order to perform an inner join with another table).

With a bit of research I found a brilliant solution here, by Even Mein (Why re-invent the wheel!)

This solution involves creating a function as follows:

CREATE FUNCTION [dbo].[fn_StripCharacters]
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
    SET @MatchExpression =  '%['[email protected]+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String


The function can be called as part of the SQL query as follows:

Alphabetic only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('[email protected]3#f4$', '^a-z0-9')


SELECT dbo.fn_StripCharacters('[email protected]#f4$', 'a-z0-9')

Leave a Reply

%d bloggers like this: