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@brazilinfo.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 'K�nstler') 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 'K�nstler' (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