parsing phone number from CHAR to Integer

I am facing a very interesting challenge: We have a program which needs to find a person that has a certain phone number on our database which has quite a lot of records. The problem is that the phone numbers we have in database (Informix IDS 9.40) are saved as CHAR(20) and are in different formats - whith lots of characters like () , - / or even x letters for extensions. Part of the problem is that a lot of the records were imported from another database which had a lot of different formats. Anyway, the numbers we want to search for are in 10 digit phone numbers. How can I parse the phone numbers we got in database (CHAR(20)) so we can match them to our 10 digit numbers? Something like this select * from persons where SomeKindOfFunction(phone) = 2486773456 and it would be true if we got something like 248-677-3456 or (248)677-3456 x 431 in database. So SomeKindofFunction() would return only the first ten numeric part of the phone field in database? Any idea would be greatly appreciated !!!! Genti

Gentian Hila wrote: > I am facing a very interesting challenge: > > We have a program which needs to find a person that has a certain > phone number on our database which has quite a lot of records. > > The problem is that the phone numbers we have in database (Informix > IDS 9.40) are saved as CHAR(20) and are in different formats - whith > lots of characters like () , - / or even x letters for extensions. > > Part of the problem is that a lot of the records were imported from > another database which had a lot of different formats. > > Anyway, the numbers we want to search for are in 10 digit phone > numbers. How can I parse the phone numbers we got in database > (CHAR(20)) so we can match them to our 10 digit numbers? Something > like this > > select * from persons where SomeKindOfFunction(phone) = 2486773456> > and it would be true if we got something like 248-677-3456 or > (248)677-3456 x 431 in database. So SomeKindofFunction() would return > only the first ten numeric part of the phone field in database? > > Any idea would be greatly appreciated !!!! > > Genti if you are happy with regular expressions then I'd just install the regexp blade Cheer Paul

Gentian Hila wrote: > I am facing a very interesting challenge: > > We have a program which needs to find a person that has a certain > phone number on our database which has quite a lot of records. > > The problem is that the phone numbers we have in database (Informix > IDS 9.40) are saved as CHAR(20) and are in different formats - whith > lots of characters like () , - / or even x letters for extensions. > > Part of the problem is that a lot of the records were imported from > another database which had a lot of different formats. > > Anyway, the numbers we want to search for are in 10 digit phone > numbers. How can I parse the phone numbers we got in database > (CHAR(20)) so we can match them to our 10 digit numbers? Something > like this > > select * from persons where SomeKindOfFunction(phone) = 2486773456> > and it would be true if we got something like 248-677-3456 or > (248)677-3456 x 431 in database. So SomeKindofFunction() would return > only the first ten numeric part of the phone field in database? > > Any idea would be greatly appreciated !!!! > > Genti if you are happy with regular expressions then I'd just install the regexp blade Cheer Paul =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Thanks for your replies. Is there a regexp for Redhat ES 3.0? I was able to locate only for SUN. On Thu, Feb 14, 2008 at 11:01 PM, Paul Watson (Oninit LLC) <paul@oninit.com> wrote: > > Gentian Hila wrote: > > I am facing a very interesting challenge: > > > > We have a program which needs to find a person that has a certain > > phone number on our database which has quite a lot of records. > > > > The problem is that the phone numbers we have in database (Informix > > IDS 9.40) are saved as CHAR(20) and are in different formats - whith > > lots of characters like () , - / or even x letters for extensions. > > > > Part of the problem is that a lot of the records were imported from > > another database which had a lot of different formats. > > > > Anyway, the numbers we want to search for are in 10 digit phone > > numbers. How can I parse the phone numbers we got in database > > (CHAR(20)) so we can match them to our 10 digit numbers? Something > > like this > > > > select * from persons where SomeKindOfFunction(phone) = 2486773456> > > > and it would be true if we got something like 248-677-3456 or > > (248)677-3456 x 431 in database. So SomeKindofFunction() would return > > only the first ten numeric part of the phone field in database? > > > > Any idea would be greatly appreciated !!!! > > > > Genti > > if you are happy with regular expressions then I'd just install the > regexp blade > > Cheer > Paul > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > >

AFAIK the source code is there, you should be able to run make and it should just work. Cheers Paul Gentian Hila wrote: > Thanks for your replies. > > Is there a regexp for Redhat ES 3.0? I was able to locate only for SUN. > > On Thu, Feb 14, 2008 at 11:01 PM, Paul Watson (Oninit LLC) > <paul@oninit.com> wrote: >> Gentian Hila wrote: >>> I am facing a very interesting challenge: >>> >>> We have a program which needs to find a person that has a certain >>> phone number on our database which has quite a lot of records. >>> >>> The problem is that the phone numbers we have in database (Informix >>> IDS 9.40) are saved as CHAR(20) and are in different formats - whith >>> lots of characters like () , - / or even x letters for extensions. >>> >>> Part of the problem is that a lot of the records were imported from >>> another database which had a lot of different formats. >>> >>> Anyway, the numbers we want to search for are in 10 digit phone >>> numbers. How can I parse the phone numbers we got in database >>> (CHAR(20)) so we can match them to our 10 digit numbers? Something >>> like this >>> >>> select * from persons where SomeKindOfFunction(phone) = 2486773456>>> >>> and it would be true if we got something like 248-677-3456 or >>> (248)677-3456 x 431 in database. So SomeKindofFunction() would return >>> only the first ten numeric part of the phone field in database? >>> >>> Any idea would be greatly appreciated !!!! >>> >>> Genti >> if you are happy with regular expressions then I'd just install the >> regexp blade >> >> Cheer >> Paul >> >> =========================================================================================== >> Please access the attached hyperlink for an important electronic communications disclaimer: >> >> http://www.oninit.com/home/disclaimer.php >> >> =========================================================================================== >> >>

AFAIK the source code is there, you should be able to run make and it should just work. Cheers Paul Gentian Hila wrote: > Thanks for your replies. > > Is there a regexp for Redhat ES 3.0? I was able to locate only for SUN. > > On Thu, Feb 14, 2008 at 11:01 PM, Paul Watson (Oninit LLC) > <paul@oninit.com> wrote: >> Gentian Hila wrote: >>> I am facing a very interesting challenge: >>> >>> We have a program which needs to find a person that has a certain >>> phone number on our database which has quite a lot of records. >>> >>> The problem is that the phone numbers we have in database (Informix >>> IDS 9.40) are saved as CHAR(20) and are in different formats - whith >>> lots of characters like () , - / or even x letters for extensions. >>> >>> Part of the problem is that a lot of the records were imported from >>> another database which had a lot of different formats. >>> >>> Anyway, the numbers we want to search for are in 10 digit phone >>> numbers. How can I parse the phone numbers we got in database >>> (CHAR(20)) so we can match them to our 10 digit numbers? Something >>> like this >>> >>> select * from persons where SomeKindOfFunction(phone) = 2486773456>>> >>> and it would be true if we got something like 248-677-3456 or >>> (248)677-3456 x 431 in database. So SomeKindofFunction() would return >>> only the first ten numeric part of the phone field in database? >>> >>> Any idea would be greatly appreciated !!!! >>> >>> Genti >> if you are happy with regular expressions then I'd just install the >> regexp blade >> >> Cheer >> Paul >> >> =========================================================================================== >> Please access the attached hyperlink for an important electronic communications disclaimer: >> >> http://www.oninit.com/home/disclaimer.php >> >> =========================================================================================== >> >> =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================