Case Insensitive SQL Select Query with WHERE clause
Mostly, in SQL you will come across to query used with a WHERE clause for searching records. But there are instances where some data may contain characters that are either lowercase or uppercase thus you need to develop case insensitive SQL queries.
-- uppercase format
select * from users where upper(username) = 'JOHN';
-- lowercase format
select * from users where lower(username) = 'john';It may happen to you sometimes that you have a database that contains multiple tables. And in the table rows, there may be some values that are not in uppercase or lowercase format. If you want to execute a SELECT query with where condition and you have the value in uppercase or lowercase format. It will not match exactly with the values of the column on which you are applying the WHERE clause.
To overcome the above problem we can use the upper() or lower() function and the query will be executed by ignoring the case sensitivity.
Using upper() function
Convert column values to uppercase when searching for the values of a column. This will take the values in uppercase format and then show you the result by ignoring cases.
SQL query example - WHERE clause
select * from users where upper(username) = 'JOHN';SQL query example - WHERE clause and LIKE statement
select * from users where upper(username) like '%ANG%';Using lower() function
You can also use the lower() function for the same purpose. This will also make the SELECT statement case insensitive.
SQL query example - WHERE clause and lower() function
select * from users where lower(username) = 'JOHN';Using WHERE clause, LIKE and lower() function
select * from users where lower(username) like '%ANG%';