How to find special characters in DB2?

Question

I have a DB2 database containing millions of records. I found that some char() or varchar() fields contain special characters which shouldn't be stored. I guess application received broken data or some code made it.

Anyway, I want to find records that have these broken data, which are special characters (not alphabetic).

I tried to find the way using query but couldn't. Does someone know the good query or advice?

Answer 1

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

Answer 2

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)