as400 interactive sql phone number search

Hi,

I have a 70 column character field. I need to find any phone numbers stored in this field. Can someone advise me on the best way to do this with interactive sql.  I have tried like with no success (sample below):

SELECT * FROM INMISC                
WHERE  (INVRB LIKE '### ### ###')    
  OR   (INVRB LIKE '###-###-###')    
  OR   (INVRB LIKE '#########')      
  OR   (INVRB LIKE '##########')

Thank you,
Lynn
lynn_harrisAsked:
Who is Participating?
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.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Lynn,

DB2 doesn't natively support regular expressions.  But with a little trick, you can extract most of the phone numbers.

Use the TRANSLATE function to change all of the numeric characters to the same character.  Then test the result.

WHERE  (translate (INVRB, '123456789', '000000000') LIKE '000 000 0000')    
  OR   (translate (INVRB, 123456789', '000000000') LIKE '000-000-0000')    
  OR   (translate (INVRB, '123456789', '00000000') LIKE '0000000000')      
  OR   (translate (INVRB, '123456789', '0000000000') LIKE '0000000000') 

Open in new window


Good Luck!
Kent
1

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
lynn_harrisAuthor Commented:
Great!

thanks
0
Dave FordSoftware Developer / Database AdministratorCommented:
Since I believe you're using DB2 for i (a.k.a. AS/400), regular expressions are supported as of v7.1. (see the article below)

http://www.itjungle.com/fhg/fhg051915-story01.html

HTH,
DaveSlash
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Kent OlsenData Warehouse Architect / DBACommented:
Very cool, Dave!  I had no idea!

Tell me again why SQL Server is everybody's favorite?
0
Dave FordSoftware Developer / Database AdministratorCommented:
> Tell me again why SQL Server is everybody's favorite?

That's easy. It's comparatively cheap, and the GUI is "built-in" to the product ... whereas in DB2, the GUI is somewhat of an afterthought ... and all the "real" experts go directly to the command-line, anyway.
0
Dave FordSoftware Developer / Database AdministratorCommented:
My employer is still stuck at v6.1, so I've never had the opportunity to try those regular expressions in my SQL, but Kent's solution works beautifully for me.

select oneBigVarchar 
  from deleteme

ONEBIGVARCHAR
this is not a phone number 1234
this contains a phone number 123-456-7890
this might contain a phone number (123) 456-7890
this contains a phone number 123 456 7890
this does not x23 456 7890
this does 123-456-7890

select oneBigVarchar
  from deleteme
where translate (oneBigVarchar, '000000000', '123456789') like '%000 000 0000%'
   or translate (oneBigVarchar, '000000000', '123456789') like '%000-000-0000%'
   or translate (oneBigVarchar, '000000000', '123456789') like '%(000) 000-0000%'

ONEBIGVARCHAR
this contains a phone number 123-456-7890
this might contain a phone number (123) 456-7890
this contains a phone number 123 456 7890
this does 123-456-7890

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:
>> That's easy. It's comparatively cheap, and the GUI is "built-in" to the product ...

That's been one of my pet peeves with it.  Because it's GUI based, anyone that can spell D-B-A thinks he is one.  I don't think that I've ever been around a SQL Server setup where the DBA was truly qualified.  In fact, it's been common to see web developers serving as the DBA and the database architect.  That plan works just fine, until the day it doesn't.  Then it's too late.
2
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
IBM System i

From novice to tech pro — start learning today.

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.