question

Just for fun, I thought of handling it by using the cteTally which would return a string with valid characters only i.e. [0-9] and [A-Z] ([a-z] are handled by using the UPPER function, but can be handled the same way as [A-Z]) DECLARE @pstring VARCHAR(200) SELECT @pstring = 'ABC 456 # & 8 ' ; WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , cteValidCharacters(CharValue) AS (--==== This provides the list of the valid Characters i.e [0-9] AND [A-Z] SELECT CHAR(47+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 UNION ALL SELECT TOP 26 CHAR(64+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E2 ) , cteAllCharacters(Chars) AS ( SELECT SUBSTRING(@pString ,N,1) FROM cteTally WHERE UPPER(SUBSTRING(@pString ,N,1)) IN (SELECT [CharValue] FROM cteValidCharacters) ) SELECT (SELECT [Chars] [*] FROM cteAllCharacters FOR XML PATH('')) It can be turned into an inline function to scale up.

For whatever replacements and matching in case you can use CLR, the most siple thing is to use a CLR RegEx implementation. You can take a look on [SQL Server Regular Expressions – Replacements][1] for sample implementation. For your concrete needs you can use the `"(?:[^a-zA-Z0-9]|\s)"` regular expression which returns all chars, which are not alphanumeric or are white space. In the sample implementation you can simple replace by empty string and you are done. [1]: http://www.pawlowski.cz/2011/05/sql-server-regular-expressions-replacements/

We get what appears to be "unvalidated" data from some of our vendors. Our database is based on the requirements of MS-Dynamics which is much more restrictive. Sometimes we get strange characters in and I use the following script to identify the data. By the way, I wrote this for SQL-2K so there are probably better ways of doing this now.
DECLARE @TestString VARCHAR(30), @Bin VARBINARY(255) SELECT @TestString = RTRIM(SomeCharColumn) FROM DB..Table (NOLOCK) WHERE LEN(SomeCharColumn) = 4 AND SomeCharColumn LIKE '%-DT' -- Sometimes this won't print (normally happens when there is null data) PRINT @TestString SET @Bin = CONVERT(VARBINARY(255), @TestString) PRINT @Bin