Lincoln Yeoh wrote:
make sure the table stores as text rather than as numeric data.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.
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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
