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

问题描述

我有一个包含数百万条记录的DB2数据库。
我发现一些char（）或varchar（）字段包含不应该被存储的特殊字符。
我猜应用程序收到破碎的数据或一些代码。

无论如何，我想查找有这些破损数据的记录，这是特殊字符字母）

我试图找到使用查询但不能使用的方式。
有人知道好的查询或建议吗？

解决方案

您可以使用DB2 TRANSLATE （）函数来隔离非字母数字字符。请注意，在Oracle兼容模式下，这将不起作用，因为在这种情况下，DB2将将空字符串视为空值，如Oracle所做。

  SELECT * 
 FROM yourtable 
 WHERE LENGTH（TRANSLATE（TRANSLATE（
 yourcolumn，
''， -  empty string 
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
）） > 0  - 将ASCII字符翻译成空字符串
  - 仍然有东西
  

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?

解决方案

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屋！