How to find special characters in DB2?

You can use the DB2 TRANSLATE() function to isolate non-alphanumeric characters. Note that this will not work in the Oracle compatibility mode, because in that case DB2 will treat empty strings as NULLs, as Oracle would do.

SELECT *
FROM yourtable
WHERE LENGTH(TRANSLATE(
  yourcolumn,
  '', -- empty string
  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
)) > 0 -- after translating ASCII characters to empty strings 
       -- there's still something left

I know this is an older thread...but after reading a ton...this was my exact problem and here is the solution I came up with to determine the problem rows...so that I could go in and manually fix them. FYI - the problem for me happens because users are copy/pasting from Word into my app. Yes I know we should fix that before ever saving...but we have bigger fish to fry.

SELECT * FROM TABLE_A where ASCII(TRIM(TRANSLATE( COLUMN_A, ' ', -- empty string '()<>!;%$#*?@+&^=-":/''.,0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ))) not in (10,64)

Some Notes:

• We use iSeries DB2 and this works great
• Make sure to keep all the spaces intact in the translate function...it needs 1 space for ever character you use
• In the 3rd parameter of the translate function there are 2 single quotes next to each other and the first one simply escapes the other (for those that may not know)

You can use a regular expression in order to retrieve the invalid characters. However this process is very expensive, because you have to read all the data, and then process it.

In order to use regular expressions in DB2, you have to adapt the environement, because this functionality is not available for SQL in the installation. You have these three options:

• Use Regular expressions with xQuery instead of normal SQL. http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/xqrregexp.html
• Define external C stored procedures as described in this article: http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html
• If you undestand Japannese, here you have a good article explaining how to use RegEx in xQuery https://www.ibm.com/developerworks/jp/data/library/db2/j_d-regularexpression/ You could only download the sources and install them. With the few examples in latin characters, I think you could understand how to use this.

Once you have defined a regular expression to ignore the valid characters (something like /[^a-zA-Z0-9]/ ), then you could executed in the database. Remember to retrieve other column where you can detect the row (for example a column ID) and then perfom updates or delete to prune the invalid characters.

If you do not know how to use regular expression, here you have a good source of information: http://www.regular-expressions.info/ Specially http://www.regular-expressions.info/charclass.html

There is a related question about regular expression: Regular Expressions in DB2 SQL