RE: [GENERAL] Case insensitive searches

On Thu, 6 May 1999, Michael J Davis wrote:

> Try:
>
> select some_field
> from table
> where lower(another_field) like '%substring_entered_by_user%'
>
> -----Original Message-----
> From: Paulo Parola [SMTP:pgsql(at)brazilinfo(dot)com]
> Sent: Thursday, May 06, 1999 3:53 PM
> To: pgsql-general
> Subject: [GENERAL] Case insensitive searches

Michael, I stuck with this format for awhile, in the interests of trying
to write generic code and then the '~*' operator was just too handy. ;-)

SELECT * FROM foo WHERE bar ~* 'kun';

Will return `Kunstler' from table `foo'...I don't know about the umlaut
though...

Cheers,
Tom

> Hi,
>
> When using mSQL I can perform case insensitive queries by using
> statements
> like below:
>
> select some_field
> from table
> where another_field clike '%substring_entered_by_user%'
>
> How should I do that with PostgreSQL? (I understand 'clike' is not a
> standard SQL feature and there is no similar in PostgreSQL).
>
> If I change the substring entered by the user, lets say for example
> 'more',
> to the following form '[mM][oO][rR][eE]' would it work?
>
> And if I have words with accents (lets say 'Knstler') how should I
> do to
> return the same entries no matter if the user types the accent or
> not? In
> the previous case, the search should return the same values no
> matter if the
> user entered the word 'Knstler' (with accent) or if he typed
> 'Kunstler'
> (without accent).
>
> TIA,
> PAulo
>
>
>

----
North Richmond Community Mental Health Center

Thomas Good Information Systems Coordinator
E-Mail: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056

Empowered by PostgreSQL 6.3.2