Re: Regex Query Index question - Mailing list pgsql-general
From | David Johnston |
---|---|
Subject | Re: Regex Query Index question |
Date | |
Msg-id | 1EBCDA78-0C15-4C12-8B99-232CEDF23CAC@yahoo.com Whole thread Raw |
In response to | Regex Query Index question (Naoko Reeves <naokoreeves@gmail.com>) |
Responses |
Re: Regex Query Index question
(David Johnston <polobo@yahoo.com>)
|
List | pgsql-general |
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 parenthesis instead of open square blacket ) :
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\(123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}' || '7890')
It doesn't use Index....
Particularly with RegEx you want to tell people what you are trying to do and not just give the expressions themselves.co-worker suggested me to use chr(40) instead so I tried this:
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\' || chr(40) || '123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}' || '7890')
No success...
Also { and period doesn't seems to use index either.... but } ) [ ] $ # works.Could you guide me to right direction for me please?
pgsql-general by date: