How to Ignore Case While Searching for a String in SQL?

1. Introduction

When performing searches in a database, case sensitivity can affect the results of the queries.

In this tutorial, we’ll learn to perform case-insensitive searches in SQL using PostgreSQL, SQL Server, and MySQL.

2. Why Is It Important?

Performing case-insensitive searches is crucial in many real-world applications. Users often expect search functions to work regardless of case differences, such as when looking for names, product titles, or email addresses. Therefore, properly handling case sensitivity improves user experience and minimizes missed data.

For example, a user’s email address might be stored in the database as [email protected]. However, since email addresses are generally case-insensitive, we expect to retrieve this record when searching for [email protected], regardless of the casing used in the query.

We’ll demonstrate how to implement a case-insensitive partial search for strings. A partial search enables us to find records where the search term matches a portion of the field’s value rather than requiring an exact match. Using the Course table from the Baeldung University database, we’ll explore how to search courses by name, showcasing standard query methods and database-specific solutions.

To showcase better results, we’ll add a new course to the table:

INSERT INTO Course values 
('CS119', 'OPERATING SYSTEM Principles', 'OS by Abraham Silberschatz', 7, true)

For this tutorial, we assume that case-insensitive settings are disabled at the database level. Typically, both MySQL and SQL Server default to case-insensitive behavior for string data, whereas PostgreSQL is case-sensitive by default for string comparisons.

3. Using the UPPER/LOWER Functions

A common method for case-insensitive partial string searches in SQL is to combine the UPPER() or LOWER() functions with the LIKE operator. This approach ensures compatibility across all standard SQL databases, including SQL Server, MySQL, and PostgreSQL.

Let’s write a query to search for a course name that contains the string operating:

SELECT id, name 
FROM Course 
WHERE LOWER(name) LIKE '%operating%'

When we execute this query, we get the result:

+-------+---------------------------------------------+
|  id   |                    name                     |
+-------+---------------------------------------------+
| CS111 | Introduction to Operating Systems           |
| CS112 | Introduction to Real Time Operating Systems |
| CS211 | Operating Systems: Intermediate             |
| CS212 | Real Time Operating Systems: Intermediate   |
| CS411 | Advanced Operating Systems                  |
| CS119 | OPERATING SYSTEM Principles                 |
+-------+---------------------------------------------+

Despite the case mismatch, the query successfully returns all courses containing the term operating. It’s important to note that since the LOWER function is used, the search string must be provided in lowercase. Similarly, if we use the UPPER function, the search string must be in uppercase. Otherwise, we’ll get no matches.

Let’s now check RDBMS-specific solutions.

4. PostgreSQL

PostgreSQL offers several approaches.

4.1. Using ILIKE Operator

PostgreSQL provides the ILIKE operator with the ability to perform case-insensitive searches. It functions similarly to the LIKE operator but ignores the case of the characters it compares.

Let’s rewrite the previous query to use the ILIKE operator to search for the course names:

SELECT id, name 
FROM Course 
WHERE name ILIKE '%operating%'

This query produces the same results as the previous one. However, we don’t have to explicitly convert the case using a function. As shown, the syntax of ILIKE is similar to that of the LIKE operator.

4.2. Using Regular Expression

PostgreSQL provides robust built-in regular expressions, which we can use to search for names. To perform a case-insensitive search, we can use the ~* operator:

SELECT id, name 
FROM Course 
WHERE name ~* 'OPERATING'

This query matches any names containing OPERATING, regardless of the case.

The benefit of this method is that it allows us to create complex regular expressions to handle various scenarios. However, those use cases are beyond the scope of this article and can be explored further in the PostgreSQL documentation.

5. MySQL

Now, let’s explore MySQL-specific ways to achieve this requirement.

5.1. Using REGEXP_LIKE Function

The REGEXP_LIKE function in MySQL allows the use of regular expressions in queries:

SELECT id, name 
FROM Course 
WHERE REGEXP_LIKE(name, 'OPERATING', 'i');

The REGEXP_LIKE function takes three parameters:

• The column name (or the value to apply the expression on)
• The pattern to match
• The match option. The i option is for case-insensitive searches, while c is for case-sensitive searches

Although REGEXP_LIKE supports complex patterns, this example demonstrates a simple partial search.

5.2. Using COLLATE

Another option is to use the COLLATE clause while executing the queries. Collation defines the rules determining how string data is sorted and compared, including case sensitivity, accent sensitivity, and character set.

Let’s build the query for case insensitive search:

SELECT id, name 
FROM Course 
WHERE name COLLATE utf8mb4_general_ci LIKE '%OPERATING%';

In this query, the COLLATE utf8mb4_general_ci ensures that the comparison is case-insensitive even if the column’s default collation is case-sensitive. The COLLATE clause provides flexibility in handling different types of string comparisons without altering the underlying column or database collation.

6. SQL Server

SQL Server also offers the COLLATE clause to adjust the search behavior:

SELECT id, name 
FROM Course 
WHERE name COLLATE Latin1_General_CI_AS like '%OPERATING%'

In this case, we explicitly specify the Latin1_General_CI_AS collation, which enables case-insensitive comparison.

7. Summary

Let’s summarize these approaches:

The UPPER/LOWER functions are widely supported but can bypass indexes if a functional index hasn’t been created with the respective function. While MySQL and PostgreSQL support functional indexes, SQL Server doesn’t.

Regular expressions offer flexibility but are slower and not index-friendly.

While index-friendly, the COLLATE clause is unavailable for partial searches in PostgreSQL, but we can use it in MySQL and SQL Server. When using the COLLATE clause, selecting the right collation is crucial to ensure comparisons align with the case sensitivity and language requirements. For example, a case-insensitive collation like utf8mb4_general_ci in MySQL or a specific case-insensitive collation in SQL Server can help achieve consistent and efficient searches.

8. Conclusion

In this article, we explored various methods to perform case-insensitive searches in SQL.

While regular expressions are powerful, they may be less efficient for large datasets than indexed searches with ILIKE or LIKE.