Re: regular expressions in query - Mailing list pgsql-general

From Lincoln Yeoh
Subject Re: regular expressions in query
Date
Msg-id 5.2.1.1.1.20050213193924.03cee310@localhost
Whole thread Raw
In response to Re: regular expressions in query  (Russ Brown <pickscrape@gmail.com>)
List pgsql-general
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.



pgsql-general by date:

Previous
From: Russ Brown
Date:
Subject: Re: regular expressions in query
Next
From: "J. Greenlees"
Date:
Subject: Re: regular expressions in query