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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

which platform and which version of db2?
Kent OlsenDBACommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 OlsenDBACommented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.