Re: accented vowels, regexp, headache...

On Fri, 3 Nov 2000 fabrizio.ermini@sysdat.it wrote: > Hi all... > As I said, I'm getting a wondrous headache trying to fix some > problem in confronting strings with accented vowels. > > I'm talking about an Italian site that has a database-driven catalog > (postgresql 6.5.3 + php 4.0.2 - upgrading is not an option). > I have to made a case insensitive search confronting a field with a > generic string (that is a fragment of a larger text). > > First attemp: for generality, I use the upper() SQL functions that's > reasonably portable among DBMS: > > $fragment= strtoupper(function(...)); > $query="Select * from table where upper(field)='$fragment'" > $result=... > > This doesn't work when $fragment contains accented vowels, > because PHP's strtoupper and PGsql's upper behave differently (it's > a matter of respecting the LOCALE, I guess), and the equal test is > not satisfied when it should. > > Second attempt: let's leave behind generality and use PGsql's > advanced operators: > > $fragment= function(...); > $query="Select * from table where field~*'$fragment'" > $result=... > > "~*" operator (case insensitive regex match) works perfectly even > with accented vowels. But... this operator assumes that the right > hand member of the test is a regexp; Since "fragment" is a generic > piece of text, there's good probability that it contains some > characters interpreted as "special" by the regexp engine (most > notably, parentheses), the test fails or it even gives an error (like > "mismatched parentheses" and similar ones). > > Now I'm a little short of ideas... what should I do: > -solve upper issues, maybe by tinkering with LOCALE, or by > susbtituting "by hand" accented vowels? > -track and substitute regexp's special characters in $fragment? > > or there's some other simpler way i'm not seeing at the moment? > Any hint will be really appreciated! select * from table where upper(field) = upper('%$fragment%') should give the desired result and doesn't care whether you have LOCALE settings right or wrong. -- Jouni Ahto