Re: parsing phone number from CHAR to Integer

On Thu, 2008-02-14 at 22:44 -0500, Gentian Hila wrote: > 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 !!!! Warning: The following code may cause nausea, nose bleeds, and headache. Use at your own risk. :) select * from customer where substr(replace(replace(replace(phone,"-",""),"(",""),")",""),1,10) = "2486773456" Hope this helps, -- Carsten Haese http://informixdb.sourceforge.net

Carsten Haese wrote: > On Thu, 2008-02-14 at 22:44 -0500, Gentian Hila wrote: >> 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 !!!! > > Warning: The following code may cause nausea, nose bleeds, and headache. > Use at your own risk. :) > > select * from customer > where substr(replace(replace(replace(phone,"-",""),"(",""),")",""),1,10) > = "2486773456"> > Hope this helps, > I would create a functional index on it... Applying this (or any other function) will disable any index you may have using the phone number column... If you create a functional index and use the same function in your queries it would be easy and fast... Version 9.x+ required... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...