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.20050215211154.03801490@localhost
Whole thread Raw
In response to Re: regular expressions in query  ("J. Greenlees" <jaqui@telus.net>)
List pgsql-general
But that method would be specific for searches for the last 4 digits. It
won't work as well for the general case of the last X digits.

To clarify the method I suggested:

Say a phone number is: 818 9567 1234

You reverse the number and store it as text and index it as

43217659818

Then if someone searches for 5671234 you reverse the query string and do a
search for

select * from phonebook where number like '4321765%' and ....

If they enter just the last 5 digits: 71234

select * from phonebook where number like '43217%' and ....

These sort of searches are indexable on postgresql.

Link.

At 04:33 AM 2/13/2005 -0800, J. Greenlees wrote:

>Lincoln Yeoh wrote:
>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.
>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



pgsql-general by date:

Previous
From: John Sidney-Woollett
Date:
Subject: Re: Slony uninstall info/warning
Next
From: "David Parker"
Date:
Subject: Re: [Slony1-general] Re: Slony uninstall info/warning