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)

Answer 3

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

Answer 4

If by special characters you mean non-printable characters you could use this one:

select yourfield, hex(trim(yourfield)),TRANSLATE(
  yourfield,
  ' ', 
  x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F')
from yourtable
where yourfield <> TRANSLATE(
  yourfield,
  ' ', 
  x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F')

You'll probably see that your odd characters HEX() is < 40.

Anyway if you know the HEX() of your odd/special characters you could use this approach and then replace them for a space or whatever you want permanently:

Update yourtable
set yourfield= Translate(yourfield, 
                            ' ', 
                            x'000102030405060708090A0B0C0D0E101112131415161718191A1B1C1D1E202122232425262728292A2B2C2D2E303132333435363738393A3B3C3D3E0F1F2F3F')
where yourfield <> Translate(yourfield, 
                            ' ', 
                            x'000102030405060708090A0B0C0D0E101112131415161718191A1B1C1D1E202122232425262728292A2B2C2D2E303132333435363738393A3B3C3D3E0F1F2F3F')

Answer 5

This query has worked for me in the past on iSeries DB2.

select * from db/table where posstr(field, x'3F') > 0 

Trouble is you have to be certain of the hex value you are searching for in the string. I had a similar situation where the I was sure the hex code for the character was x'3F, but when I sub-string the non-viewable character it was actually x'22. You might want to single out the character that is giving you the issue and see what it's value is.

select hex(substr(field, 21,1)) from db/table where posstr(field, 'StringBeforeCharacter') > 0 

Answer 6

You can use below SQL which will work fine.

select col 1 from table where col1 like '%'|| chr(10) || '%';