I have written a function in DB2 – that is calculating ASCII of records in a particular column. I want to some help as I want to check the ASCII of every single character in string return yes if the ASCII of that record is greater than 127.
BEGIN ATOMIC DECLARE POS, 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';
Advertisement
Answer
Why to calculate ascii of every character in that column, if the goal is just to get such rows?
SELECT STR FROM ( VALUES 'Hello, world' , 'Привет, мир' ) T (STR) WHERE xmlcast(xmlquery('fn:matches($s, "[^x00-x7F]")' passing t.str as "s") as int) = 1;
The fn:matches function uses regular expressions.
The [^x00-x7F]
regular expression means “a character with hex value not in the 0x00 - 0x7F
interval”. If a value of passed t.str
contains such a character, the function returns 1 and 0 otherwise.