Queries with Regular Expressions
Hello!
My queries work fine with Regular Expressions, as:
SELECT field FROM table WHERE field ~ 'something';
SELECT field FROM table WHERE field ~* 'something';
SELECT field FROM table WHERE field ~* 'som[i,e]thing';
And it works fine for special characters:
SELECT field FROM table WHERE field ~* 'chão';
But I just can't make it work correctly using brackets:
SELECT field FROM table WHERE field ~* 'ch[aã]o';
It just returns tuples that have 'chao', but not 'chão'.
My queries are utf-8 an the database is SQL_ASCII.
Any idea? Thank you!
--
Silas Justiniano - Brazil
But I just can't make it work correctly using brackets:
SELECT field FROM table WHERE field ~* 'ch[aã]o';It just returns tuples that have 'chao', but not 'chão'.
My queries are utf-8 an the database is SQL_ASCII.
I suspect the bracketed expression is turning into [aXY], where XY is
the two-byte sequence corresponding to ã in UTF8. So the regular
expression is only going to match strings of the form chao, chXo and
chYo. To make sure that this is what's happening, try this:
select length('ã');
I bet you get back 2, not 1. I don't know if a UTF8 database will
handle this correctly or not. The safest thing to do may be to use
queries like this:
SELECT field FROM table WHERE field ~* 'ch(a|ã)o';
- John D. Burger
MITRE
"John D. Burger" <john@mitre.org> writes:
My queries are utf-8 an the database is SQL_ASCII.
I suspect the bracketed expression is turning into [aXY], where XY is
the two-byte sequence corresponding to � in UTF8.
That's what it looks like to me. You can hardly blame the database for
this, when you haven't clued it in that you're using UTF8.
I don't know if a UTF8 database will handle this correctly or not.
I believe that it will work in recent PG releases (7.4 and up).
regards, tom lane
John, it worked completely fine! Thank you! I don't understand exactly
the difference between [] and () for REs, but I'm starting to study
them deeply.
Thank you very much! Pg is great!
On 4/6/06, John D. Burger <john@mitre.org> wrote:
But I just can't make it work correctly using brackets:
SELECT field FROM table WHERE field ~* 'ch[aã]o';It just returns tuples that have 'chao', but not 'chão'.
My queries are utf-8 an the database is SQL_ASCII.
I suspect the bracketed expression is turning into [aXY], where XY is
the two-byte sequence corresponding to ã in UTF8. So the regular
expression is only going to match strings of the form chao, chXo and
chYo. To make sure that this is what's happening, try this:select length('ã');
I bet you get back 2, not 1. I don't know if a UTF8 database will
handle this correctly or not. The safest thing to do may be to use
queries like this:SELECT field FROM table WHERE field ~* 'ch(a|ã)o';
- John D. Burger
MITRE---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Silas Justiniano - Brazil