Sql to find non-printable characters in a string

Question

one of my SQL queries returns non-printable characters in the data due to which I get an error message. Please let me know how I can check if a string contains non-printable characters in T-SQL so that I can find these strings and correct the data? Thanks in advance.

Answer 1

I found that some columns had a CHAR (0) character that caused this problem. I replaced them with null and it worked.

Answer 2

When you don’t know which non-printable character is causing the problem, but you specified the entry:

• Management Studio
• ( ),
• ASCII-,

, , , , .

Answer 3

:

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;

, ( NumX CTE), , , , , , FOR XML PATH.