sql to find non-printable characters in a string

one of my SQL queries is returning non-printable characters in the data because of which开发者_如何学运维 I get an error in the report. Please let me know how can I check if a string has a non-printable characters in T-SQL, so that I can find those rows and fix the data? Thanks in advance.

---

Found this:

WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
UpdateCTE
AS
(SELECT keycol, DesNM,
(SELECT CASE WHEN ASCII(SUBSTRING(DesNM, n, 1))
BETWEEN 0x00 AND 0x1F
THEN ''
ELSE SUBSTRING(DesNM, n, 1)
END + ''
FROM I2DE AS B
JOIN Nums
ON n <= LEN(DesNM)
WHERE B.keycol = A.keycol
FOR XML PATH('')) AS DesNM_clean
FROM I2DE AS A)
UPDATE UpdateCTE
SET DesNM = DesNM_clean;

There are a couple parts to it, one is generating a table with numbers on the fly (using the NumX CTEs), then slicing the column to individual characters, checking each if it is in the range then skipping it, finally concatenating back to a single value using FOR XML PATH.

---

When you do not know which non-printable character is causing the problem, but you have identified the record:

1. Run a Query in Management Studio and copy the single known field with issues from the grid
2. Paste the field into a hex editor (paste into the text portion), so you can see the hex of the characters
3. Lookup the characters in question against an ASCII chart, if necessary

Now you can either do an update with a replace to scrub your data, or simply perform the cleanup in your query itself, because now you know what's causing the problem.

---

I found that some columns were having the character CHAR(0) which was causing this issue. I replaced them with null and it worked.