Search code snippets, questions, articles...

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';
Best JSON Validator, JSON Tree Viewer, JSON Beautifier at same place. Check how cool is the tool

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%';
Was this helpful?
0 Comments