Thread: Locale and LIKE matching
Hi! I'd like to know if the LIKE operator is locale-aware when matching strings. Specifically, I'd like to have "... LIKE '%a%'" match "á" (á)or "ã" (ã) (these are ISO8859-1, pt_BR chars). Is this possible? I've compiled postgresql 7.0.2 with --enable-locale --enable-multibyte, and created the DB with latin1 encoding, but it still doesn't work as I'd like. TIA -- Daniel Serodio daniel@ibnetwork.com.br
Daniel Serodio wrote: > Hi! I'd like to know if the LIKE operator is locale-aware when matching > strings. Specifically, I'd like to have "... LIKE '%a%'" match "á" (á)or > "ã" (ã) (these are ISO8859-1, pt_BR chars). Is this possible? I've > compiled postgresql 7.0.2 with --enable-locale --enable-multibyte, and created > the DB with latin1 encoding, but it still doesn't work as I'd like. > TIA I can feel your pain... The LIKE operators and its wildcard characters are a misfeature of SQL itself. Fortunately for us PostgreSQL has regular expressions as an extension to SQL -- look http://www.postgresql.org/users-lounge/docs/7.0/user/operators2123.htm. I would do ~ '.*[aáã].*' or something like it. For documentation, try man 5 regexp or at Emacs man regexp(5). -- _ / \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913 \ / Amdocs at Bell Canada +1 (514) 786 87 47 X Support Center, São Paulo, Brazil mailto:adbaamd@bell.ca / \ http://terravista.pt./Enseada/1989/ mailto:leandrod@amdocs.com
Daniel Serodio writes: > Hi! I'd like to know if the LIKE operator is locale-aware when matching > strings. Specifically, I'd like to have "... LIKE '%a%'" match "á" (á)or > "ã" (ã) (these are ISO8859-1, pt_BR chars). Is this possible? No, this is not part of standard locale support, nor the task of the LIKE operator. In general, "accent folding" is very situation dependent. If you look into the pgsql-sql archives a few days back you will find a link to a function that expands letters into a regular expression that matches all(?) accented variants of it, which may suit you. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Quoting Martín Marqués <martin@bugs.unl.edu.ar>: > On Saturday 31 March 2001 01:27, you wrote: > > Hi! I'd like to know if the LIKE operator is locale-aware when > matching > > strings. Specifically, I'd like to have "... LIKE '%a%'" match "á" > > (á)or "ã" (ã) (these are ISO8859-1, pt_BR chars). Is > this > > possible? I've compiled postgresql 7.0.2 with --enable-locale > > --enable-multibyte, and created the DB with latin1 encoding, but it > still > > doesn't work as I'd like. TIA > > Are you sure that your locale is latin1? Check with the á, because I > think > the latin1 is not for Portugues, but spanish, but..... I may be wrong. latin1 is the one for most 'accented' languages, Spanish and Portuguese included... Saludos! :) -- Daniel Serodio daniel@ibnetwork.com.br