Interpreting the results from this isn't straightforward, at least not to me. I prefer the output formatted like this:
SELECT Strings, x.Goodchars, y.Badchars
FROM #t
CROSS APPLY (
SELECT SUBSTRING(Strings,n,1)
FROM (SELECT TOP (DATALENGTH(Strings)) n = ROW_NUMBER() OVER(ORDER BY @@SPID) FROM sys.columns a, sys.columns b) n
WHERE SUBSTRING(Strings,n,1) LIKE '%[a-zA-Z0-9]%'
FOR XML PATH('')
) x (Goodchars)
CROSS APPLY (
SELECT SUBSTRING(Strings,n,1)
FROM (SELECT TOP (DATALENGTH(Strings)) n = ROW_NUMBER() OVER(ORDER BY @@SPID) FROM sys.columns a, sys.columns b) n
WHERE SUBSTRING(Strings,n,1) LIKE '%[^a-zA-Z0-9]%'
FOR XML PATH('')
) y (Badchars)
Otherwise, an interesting article. Putting ^ into the @allowed string was a bit naughty ๐
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden