Case Insensitive SQL Select Query with WHERE clause

sql Share on :

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%';

Contribute to this Snippet

Was this helpful?

Created by :

@

Write your Comment

0 Comments

Similar code snippets

Create your own code snippets and search them using our portal and chrome extension

Programming Feeds

Learn something new everyday on Devsheet

Important Links

Join Devsheet Create new snippet Ask a Question Write an article Online JSON Viewer