Identify Bad Characters in a Table Column

CREATE TABLE #t (strings VARCHAR(100));

CREATE TABLE #b (bad CHAR(1));

DECLARE @allowed VARCHAR(30)

SET @allowed = '%[^a-zA-Z0-9]%' -- Pattern to allow only alphanumerics

DECLARE @notallowed CHAR(1)

SET @notallowed = '*' -- Any non-allowed character

INSERT INTO #t (strings)

SELECT 'AABA#24$2@'

UNION ALL SELECT 'AAcc/24)2('

UNION ALL SELECT 'ABC123'

UNION ALL SELECT 'AA%(&_#24$2@'

UNION ALL SELECT 'AA+JDLASD4)2('

UNION ALL SELECT 'ABC123'

UNION ALL SELECT ''

UNION ALL SELECT '&'

UNION ALL SELECT '****'

-- Identify the bad characters

;WITH BadStrings (strings) AS (

SELECT strings+@notallowed 

FROM #t

WHERE strings LIKE @allowed

)

,Parser AS (

SELECT SUBSTRING(strings, PATINDEX(@allowed,strings), 1) as bad

,SUBSTRING(strings, 1+PATINDEX(@allowed,strings),LEN(strings)) as rest

FROM BadStrings

UNION ALL

SELECT SUBSTRING(rest, PATINDEX(@allowed,rest), 1) 

,SUBSTRING(rest, 1+PATINDEX(@allowed,rest), LEN(rest))

FROM Parser WHERE LEN(rest) > 0 and 

SUBSTRING(rest, 1+PATINDEX(@allowed,rest), LEN(rest)) <> ''

)

SELECT DISTINCT bad 

FROM Parser b

DROP TABLE #t

DROP TABLE #b