>
> Not testing here but... and ignore whitespace
>
> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \d{3} [ -\s ] \d{4} )$'
>
Some tweaks needed but seriously consider dropping RegEx and going the
functional index route.
> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \s* \d{3} \s* [ -\s ] \s* \d{4} )$'
Now that I read more closely the alternation is actually concatenation. My
point still stands but your issue is that you have not created a functional
index on the decryption result of the encrypted phone number. PostgreSQL does
not know that the decrypted phone number is equivalent to the une
Naoko Reeves writes:
> Also forgot to mentioned the version:
> select version() >> "PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
> i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
Oh --- there's your problem. In 8.4 and earlier, we don't trust \( to be
a lite
On Aug 11, 2011, at 18:26, Naoko Reeves wrote:
> Hello,
> I have query phone number in database as follows:
> [123) 456-7890
>
> (123) 456-7890
>
Store phone numbers without formatting...the data is the numbers themselves the
formatting is presentation.
> When I query like this:
>
> SELECT
Tom,
Thank you for your quick reply. Data start with "(123" only returns 28
records where as phone number start with"[123" returns 1.
Changed the data so that both will return 1 row.
One with "(999" query takes about 30 seconds (30983ms) without index.
One with "[999" take about 28 ms with index.
Naoko Reeves writes:
> I have query phone number in database as follows:
> [123) 456-7890
> (123) 456-7890
> When I query like this:
> SELECT * FROM phone
> WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}'
> || '7890')
> it use Index but if I query like this (notice
Hello,
I have query phone number in database as follows:
[123) 456-7890
(123) 456-7890
When I query like this:
SELECT * FROM phone
WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}'
|| '7890')
it use Index but if I query like this (notice first character is
open paren
