sql查找字符串中不可打印的字符

Question

我的一个 SQL 查询在数据中返回不可打印的字符，因此我在报告中收到错误。请让我知道如何检查字符串在 T-SQL 中是否具有不可打印的字符，以便我可以找到这些行并修复数据？提前致谢。

Answer 1

Found这个:

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.