如何在 DB2 中查找特殊字符? [英] How to find special characters in DB2?

问题描述

我有一个包含数百万条记录的 DB2 数据库.我发现某些 char() 或 varchar() 字段包含不应存储的特殊字符.我猜应用程序收到了损坏的数据或某些代码.

I have a DB2 database containing millions of records. I found that some char() or varchar() fields contain special characters which shouldn't be stored. I guess application received broken data or some code made it.

无论如何，我想找到包含这些损坏数据的记录，这些数据是特殊字符(不是字母).

Anyway, I want to find records that have these broken data, which are special characters (not alphabetic).

我尝试使用查询找到方法，但找不到.有人知道好的查询或建议吗?

I tried to find the way using query but couldn't. Does someone know the good query or advice?

推荐答案

您可以使用 DB2 TRANSLATE() 函数来隔离非字母数字字符.请注意，这在 Oracle 兼容模式下不起作用，因为在这种情况下，DB2 会将空字符串视为 NULL，就像 Oracle 所做的那样.

You can use the DB2 TRANSLATE() function to isolate non-alphanumeric characters. Note that this will not work in the Oracle compatibility mode, because in that case DB2 will treat empty strings as NULLs, as Oracle would do.

SELECT *
FROM yourtable
WHERE LENGTH(TRANSLATE(
  yourcolumn,
  '', -- empty string
  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
)) > 0 -- after translating ASCII characters to empty strings 
       -- there's still something left

这篇关于如何在 DB2 中查找特殊字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！