SQL select rows case INsensitive not works my statement-CodePudding

I have Android app that sends to server pattern to find user by name. I want to be able to find rows where VARCHAR column is like pattern (but case INsensitive) that I send to server. Lets say we have a nickname like 'Bigboss'. The SQL statement should be able to find this row either when is passed 'Bigboss' or 'bIGBOSS' or 'biGbOsS' or 'bi' etc. Currently it works weird.

Here is my SQL statement:

SELECT * 
from users_details 
where upper(name) like '"   pattern   "%' 
   or lower(name) like '"   pattern   "%'

Help me please.

CodePudding user response：

SELECT * 
  from users_details 
 where name ilike '"   pattern   "%'

see the manual :

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale

CodePudding user response：

Can you try this.

SELECT * from users_details where upper(name) like '%PATTERN%'

If you are passing pattern dynamically then like pattern.touppercase()