How Can I Perform Case-Insensitive String Comparisons in PostgreSQL?

Case Insensitive String Comparison in PostgreSQL

In PostgreSQL, sometimes you may need to perform case-insensitive string comparisons for more flexible data matching.

One way to achieve this is by using the ilike operator, which is similar to like, but ignores case differences. For instance:

SELECT * 
WHERE email ilike '[email protected]'

Note that ilike uses the backslash character for escaping special characters. To use other characters like [ or ], you can use the replace() function to escape them.

For example:

WHERE email ilike replace(replace(replace(, '~', '~~'), '%', '~%'), '_', '~_') escape '~'

Alternatively, you can create a function for escaping text before performing the ilike comparison.

For comparing against an array of case-insensitive values, you can use any():

WHERE email ilike any(array['[email protected]', '[email protected]'])

With these methods, you can perform case-insensitive string comparisons in PostgreSQL, providing added flexibility in your queries.

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