I came across some non printable characters during an extract from a database. Some columns had control characters like TAB, BS, LF and so on. When these got into the extract they caused some downstream processes to fail because the TAB character was also used as a delimiter. I now have to figure out where these bad characters came from and eliminate them but I was able to quickly fix the feed by applying this function in my extract code.
I use the function in the following way
CASE WHEN dbo.HasNonPrintableChar(column_1) = 0 THEN column_1 ELSE NULL END AS column_1
I am now assured that I will only pass printable ASCII in the output of my query. The function uses the ASCII function and searches for values outside of the printable range. Enjoy!