DB2 sql query to find non ascii characters in stri

Question

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.

Answer 1

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

And then write:

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

(Disclaimer: completely untested.)

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.