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