Обсуждение: 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