Re: regular expressions in query

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.