Re: regular expressions in query

Lincoln Yeoh wrote:
> At 09:57 AM 2/13/2005 +0000, Russ Brown wrote:
>
>> I've thought about things like this in the past, and a thought that
>> occurred to me was to add a functional index on just_digits(telephone)
>> to the table. Would this not allow the above query to use an index
>> while searching?
>
>
> I think it should. But for phone numbers it may be better to reverse the
> digits before indexing - usually whilst the area code changes, the last
> 4 or 5 digits don't change.
>
> This way you can do a LIKE search on *5678. Where the number ends with
> 5678.
>
> I'm not sure how to get Postgresql to index from the ending to the start
> of a string vs the normal from the start to the end, so in my webapp I
> reversed it at the application layer. If you are going to do this sort
> of thing at the application layer you might as well do the nondigit
> removal there too.
>
> e.g.
> $phone=~tr/0-9%_//cd; # I allowed the wildcards % and _
> $phone=reverse $phone;
>
> You may still wish to store the phone numbers "as is" for display purposes.
>
> Link.
>
make sure the table stores as text rather than as numeric data.
then you can use the excellent perl string tools to pull the last 4
characters of the number.

$base=((strlen-4,strlen)

$base being the last 4 digits.
then convert to numeric to test against search requirements.

Jaqui