Find A field not having 9 digits

How do I find rows where acct_num field does not have 9 digits? I am trying this but not working --

select * from pfnd.ACCTS s where s.acct_num not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
Who is Participating?
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Sunny,

You're looking for bad data, huh?  :)

You could always cast it as an integer, but that will throw an exception when it fails and that can be hard to trap in a lot of SQL clients.

Perhaps the shortest of the universal solutions is to replace all of the digits with something else, then test the resulting string.

Good Luck,

SELECT {primary_key}
FROM {mytable}
WHERE translate (source_field, '**********', '0123456789') <> '*********';

Open in new window

which platform and which version of db2?
sunny82Author Commented:
It is DB2 version 9.7. Is there any reg exp which I can write which can check for the bad data?
Kent OlsenData Warehouse Architect / DBACommented:

There's regular expression support for XML, and I believe that some support can be found in DB2 for Z/OS.

If you're using UDB/LUW you'll have to install a 3rd party Java extension and write a Java based UDF.

Using the tools native to DB2, the query above is probably your best bet.

sunny82Author Commented:
ok, got it, I was initially thinking if we could use wild-cards in such a way so that I can specify every digit in the query like I used above. But it seems like it is not possible in DB2.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.