DB2 sql 查询以查找字符串中的非 ascii 字符 [英] DB2 sql query to find non ascii characters in strings

问题描述

我有一个表(比如 ELEMENTS)，其中有一个名为 NAME 的 VARCHAR 字段，编码在 ccsid 1144 中.我需要在 NAME 字段中找到所有包含非 ascii 字符"的字符串，即 ccsid 1144 字符集中没有 ascii 字符的字符.

I have a table (say ELEMENTS) with a VARCHAR field named NAME encoded in ccsid 1144. I need to find all the strings in the NAME field which contain "non ascii characters", that is characters that are in the ccsid 1144 set of characters without the ascii ones.

推荐答案

我觉得你应该可以创建这样的函数:

I think you should be able to create a function like this:

CREATE FUNCTION CONTAINS_NON_ASCII(INSTR VARCHAR(4000))
  RETURNS CHAR(1)
  DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
  BEGIN ATOMIC
  DECLARE POS, LEN INT;
  IF INSTR IS NULL THEN
    RETURN NULL;
  END IF;
  SET (POS, LEN) = (1, LENGTH(INSTR));
  WHILE POS <= LEN DO
    IF ASCII(SUBSTR(INSTR, POS, 1)) > 128 THEN
      RETURN 'Y';
    END IF;
    SET POS = POS + 1;
  END WHILE;
  RETURN 'N';
END

然后写:

SELECT NAME
  FROM ELEMENTS
 WHERE CONTAINS_NON_ASCII(NAME) = 'Y'
;

(免责声明:完全未经测试.)

(Disclaimer: completely untested.)

顺便说一句 —从文档来看，似乎 VARCHAR 是一个字节字符串，而不是 Unicode 字符.(字节范围从 0 到 0xFF；Unicode 字符范围从 0 到 0x10FFFD.)如果您对支持 Unicode 感兴趣，您可能想要使用不同的数据类型.

By the way — judging by the documentation, it seems that VARCHAR is a string of bytes, not of Unicode characters. (Bytes range from 0 to 0xFF; Unicode characters range from 0 to 0x10FFFD.) If you're interested in supporting Unicode, you might want to use a different data-type.

这篇关于DB2 sql 查询以查找字符串中的非 ascii 字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！