I recently needed to strip out non-alphanumeric characters in SQL Server. I initially thought I might be able to use a managed stored procedure and C# regular expressions to do so, but I thought the performance would be bad (e.g. you’d have to cursor through a table, extract a field value, use RegEx on it, go to the next row, etc.). So I came up with the below function using T-SQL’s quasi-regular expressions in PATINDEX:

/*******************************************************************
dbo.fnStripNonAlphaNumeric

Removes all non-alphanumeric characters (including spaces) from
@input, e.g.

select dbo.fnStripNonAlphaNumeric(‘Help, I “think” I”m falling!’)

returns

HelpIthinkImfalling

*******************************************************************/
CREATE FUNCTION dbo.fnStripNonAlphaNumeric
(
    @input varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
    
    DECLARE @i int
    DECLARE @result varchar(500)
    SET @result = @input
    SET @i = patindex(‘%[^a-zA-Z0-9]%’, @result)
    WHILE @i > 0
    BEGIN
        SET @result = STUFF(@result, @i, 1, ”)
        SET @i = patindex(‘%[^a-zA-Z0-9]%’, @result)
    END

    RETURN @result

END

Then in use it’s something like

SELECT dbo.fnStripNonAlphaNumeric(FieldWithAlphaNumerics) as AlphaCleanValue
FROM MyTable

FWIW, to strip non-alphanumeric in C# you can use the one-liner (assuming you have a initial string called “input”)

System.Text.RegularExpressions.Regex.Replace(input, @”[sW]*”, “”)

🙂