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

If you ask me – there are certain questions not appropriate for an interview as they are often very subjective. There are some questions, which really do not help to identify if the candidate has the necessary skills or not. Here is one such question, if you ask me in the interview, I may not get it right too, as this requires some experience in writing scripts as well as trial and error mechanics to get it right.

Question: How to write will remove all the special characters and parses Alpha Numeric values only?

Answer: Honestly, it is not that easy to write this script. However, if you are stars are not bright you may end up with an interviewer who believes writing this script is the ultimate test. Do not get me wrong, writing this script is not a bad test, I believe it is not the best question for the interview.

CREATE FUNCTION dbo.UDF_ParseAlphaChars(@string VARCHAR(8000)
)RETURNS VARCHAR(8000)AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINTSET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)WHILE @IncorrectCharLoc > 0BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)END
SET @string = @stringRETURN @stringENDGO

You can test above function with the help of following test:

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

The above query will return following result set:

ABCID4e5F6