Thread: RE: [GENERAL] Case insensitive searches

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

    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

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