How to find non-ASCII symbols in a string. DB2

Question

please advise on my particular issue.

I have a table field with VARCHAR type. I need to validate this field the way it DOESN'T have any non-ASCII symbols (like ╥ ї ╡ etc.) I didn't find any ways to resolve this issue.

Please give me a hand in this. Thanks in advance!

**Update: The example attached in comments can't resolve my issue. There is shown a fixed set of latin chars and numbers, but my field accepts Japanese and Chinese symbols.

"a" is also a "UTF-8 symbol". I assume you mean *non-ASCII character*?! — deceze, Sep 09 '15 at 11:11
Possible duplicate of http://stackoverflow.com/questions/17462802/how-to-find-special-characters-in-db2/17469776#17469776 — mustaccio, Sep 09 '15 at 11:53
Your question doesn't make sense...you can't mix latin, Japanese and chinese symbols in a single byte encoded character field. Is the field actually UTF-8 as originally posted? So what's a valid UTF-8 symbol and what's not? — Charles, Sep 09 '15 at 12:55

score -1 · Answer 1 · edited May 23 '17 at 12:23

Time for another silly XML trick:

SELECT 
    XMLQUERY('matches($X,"^[A-z0-9]+$")' 
    PASSING XMLTEXT('╥ї╡') AS "X"
)
FROM SYSIBM.SYSDUMMY1

  1
-----
false

See https://stackoverflow.com/a/17467695/3434508 for details on using Regular Expressions for DB2

See https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.xml.doc/doc/xqrregexp.html for advanced RegEx character classes.

How to find non-ASCII symbols in a string. DB2

1 Answers1