Thread: Help on collation and accent sensitivity

Hello,



This is probably a very basic question but I'm having a problem writing queries that should be accent insensitive. I
havepg v8.1 for Windows installed with Portuguese support and everything works fine except a very basic thing.  



An example query like the following:



select name from clients where name like '%Jose%'



should return all names including Jose or Jose' (or any other accent variation) because they are equivalent but I'm
gettingfrom pg only the ones I specifically include in the query expression. We have lots of expressions with
equivalentslike these and it is not possible to explicitly decompose a query in all possible variations. I've also
verifiedthat it sorts correctly ignoring accents and the problem is only in querying. I'm experienced with SQL Server
(nowlearning pg) and there only by choosing the appropriate collation I get case/accent sensitivity policy well
defined.Is there any similar parameter in pg that I've missed and have the same effect? 



Can anybody help me on this?



Many thanks



JLS

jlmssilva wrote:
> I'm experienced with SQL Server (now learning pg)
> and there only by choosing the appropriate collation I get
> case/accent sensitivity policy well defined. Is there any similar
> parameter in pg that I've missed and have the same effect?

You could try to define your own locale, but I'm not sure how that works
on Windows.

Rumour has it that some people to accent-insensitive comparisons using
the to_ascii function.  Since the notion of accent-sensitivity differs
between languages, it'd probably be best if you wrote your own
conversion function.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/