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