Interview Question of the Week #018 – Script to Remove Special Characters – Script to Parse Alpha Numerics

Some interview questions test the wrong thing. Ask me to write a character-cleaning function from memory, and I may need a few trials too. That doesn’t tell you much about how I design or troubleshoot a database.

Question: How do you remove special characters and keep only letters and digits in SQL Server?

Answer: First define the allowed characters. This version keeps ASCII A to Z, a to z, and 0 to 9. It uses a PATINDEX and STUFF loop, with an explicit binary collation so the range isn’t interpreted according to the database’s linguistic collation. Run it in a scratch database where the function name is free.

CREATE FUNCTION dbo.UDF_ParseAlphaChars (@string varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
    IF @string IS NULL RETURN NULL;
    DECLARE @IncorrectCharLoc int;
    SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%',
                                    @string COLLATE Latin1_General_100_BIN2);
    WHILE @IncorrectCharLoc > 0
    BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '');
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%',
                                        @string COLLATE Latin1_General_100_BIN2);
    END;
    RETURN @string;
END;
GO

SELECT dbo.UDF_ParseAlphaChars('AB"_I+{D[]}4|:e;"5,<.F>/?6') AS CleanedText;

The result is ABID4e5F6: every character outside the allowed range is gone, and the order of the rest is unchanged. Understanding the rule matters more than memorizing this loop. If names or international text must survive, ASCII-only cleaning is the wrong rule; decide the character policy with the business first.

Cleaning text is not a coding trick, it is a business rule, so decide which characters stay before you write the loop.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.