Finding hidden characters in your data using Dynamic T-SQL

On occasion you may have run into a situation where you have had extended ASCII characters in your DB fields. They could be from old data conversions or from various problems and errors in the databases. The script below can enable you to find the characters within the column.

DECLARE @tablename VARCHAR(1000) =’Part_Master’

DECLARE @columnname VARCHAR(100)=’PRTNUM_01′

DECLARE @counter INT = 0

DECLARE @sql VARCHAR(MAX)

WHILE @counter <=255

BEGIN

SET @sql=

‘SELECT ‘+@columnname+’,’+CAST(@counter AS VARCHAR(3))+’ as CharacterSet,

CHARINDEX(CHAR(‘+CAST(@counter AS VARCHAR(3))+’),

‘+@columnname+’) as LocationOfChar

FROM ‘+@tablename+’

WHERE CHARINDEX(CHAR(‘+CAST(@counter AS VARCHAR(3))+’),’+@columnname+’) <> 0′

PRINT (@sql)

EXEC (@sql)

SET @counter = @counter + 1

END