How Can I Perform Case-Insensitive Wildcard Searches in SQL?

<code>| title           |
|-----------------|
| Elm Tree         |
| elm tree         |
| Red Elm          |
| RED ELM          |
| Oak Tree         |
</code>

The query SELECT * FROM trees WHERE LOWER(trees.title) LIKE '%elm%' will return the following rows:

<code>| title           |
|-----------------|
| Elm Tree         |
| elm tree         |
| Red Elm          |
| RED ELM          |</code>

This the case-insensitive nature of the search using the LOWER function. Note that this approach converts all values ​​to lowercase before comparison, which may affect performance on very large tables. Other database systems may offer alternative case-insensitive comparison methods.

SQL case-insensitive wildcard search

Case sensitivity can be a challenge when performing wildcard searches in SQL using the LIKE operator. For example, the query SELECT * FROM trees WHERE trees.title LIKE '%elm%' will only return results if the title column contains the exact string "elm".

Solution: Use LOWER function

MySQL provides a solution to this problem using the LOWER function. By including the column in the LOWER function, the search becomes case-insensitive. Here’s how:

<code class="language-sql">SELECT * FROM trees WHERE LOWER(trees.title) LIKE '%elm%'</code>

In this query, the LOWER function converts the contents of the title column to lowercase before performing a LIKE search. Therefore, the query will return all rows whose lowercase representation of title contains the string "elm".

Example

Consider a hypothetical table called trees containing the following rows:

<code>| title           |
|-----------------|
| Elm Tree         |
| elm tree         |
| Red Elm          |
| RED ELM          |
| Oak Tree         |</code>

QuerySELECT * FROM trees WHERE LOWER(trees.title) LIKE '%elm%' will return the following rows:

<code>| title           |
|-----------------|
| Elm Tree         |
| elm tree         |
| Red Elm          |
| RED ELM          |</code>

This demonstrates the case-insensitive search feature using the LOWER function. Note that this method converts all values ​​to lowercase before comparing, which may impact performance on large tables. Other database systems may provide alternative case-insensitive comparison methods.

<code></code>

The above is the detailed content of How Can I Perform Case-Insensitive Wildcard Searches in SQL?. For more information, please follow other related articles on the PHP Chinese website!