sql to find non-printable characters in a string

Question

Hi, 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.

Answer 1

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.