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

1. IntroductionWhen 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 asSomeOnebaeldung.com. However, since email addresses are generally case-insensitive, we expect to retrieve this record when searching forsomeonebaeldung.com, 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 theCoursetable 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)CopyFor 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 FunctionsA 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 stringoperating:SELECT id, name FROM Course WHERE LOWER(name) LIKE %operating%CopyWhen 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 | ----------------------------------------------------CopyDespite the case mismatch, the query successfully returns all courses containing the termoperating. 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. PostgreSQLPostgreSQL offers several approaches.4.1. Using ILIKE OperatorPostgreSQL 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%CopyThis 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 ExpressionPostgreSQL 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 ~* OPERATINGCopyThis query matches any names containingOPERATING, 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. MySQLNow, let’s explore MySQL-specific ways to achieve this requirement.5.1. Using REGEXP_LIKE FunctionThe REGEXP_LIKE function in MySQL allows the use of regular expressions in queries:SELECT id, name FROM Course WHERE REGEXP_LIKE(name, OPERATING, i);CopyThe REGEXP_LIKE function takes three parameters:The column name (or the value to apply the expression on)The pattern to matchThe match option. Theioption is for case-insensitive searches, whilecis for case-sensitive searchesAlthough REGEXP_LIKE supports complex patterns, this example demonstrates a simple partial search.5.2. Using COLLATEAnother option is to use the COLLATE clausewhile 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%;CopyIn this query, the COLLATEutf8mb4_general_ciensures 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 ServerSQL 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%CopyIn this case, we explicitly specify theLatin1_General_CI_AScollation, which enables case-insensitive comparison.7. SummaryLet’s summarize these approaches:ApproachPostgreSQLMySQLSQL ServerUPPER/LOWERSupportedSupportedSupportedILIKE ClauseSupportedNot SupportedNot SupportedRegular ExpressionSupportedSupportedNo SupportCOLLATE ClauseNot Supported for partial searchSupportedSupportedThe 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 likeutf8mb4_general_ciin MySQL or a specific case-insensitive collation in SQL Server can help achieve consistent and efficient searches.8. ConclusionIn 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.