Thread: 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

Maybe not the fastest, but:

select some_field from table where lower(another_field) like
    lower('%substring_entered_by_user%');


That will lower case the search field and item to do the match against.

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Specialist
Email:  aholtz@bright.net
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------


On Thu, 6 May 1999, Paulo Parola wrote:

>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
>
>
>