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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://www.itjungle.com/fhg/fhg051915-story01.html
HTH,
DaveSlash
Very cool, Dave! I had no idea!
Tell me again why SQL Server is everybody's favorite?
Tell me again why SQL Server is everybody's favorite?
> 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.
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.
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
>> 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.
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.
ASKER
thanks