Also, certain operations are not possible with When considering the full power of Unicode and its many special cases, Nondeterministic collations give a more “correct” behavior, especially User-defined collations are deterministic by default. More interestingĮxamples make use of the ICU customization facilities explained above.įor example: CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false) ĬREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false) Īll standard and predefined collations are deterministic, all In particular, this would allow strings inĭifferent normal forms to be compared correctly. This example would use the standard Unicode collation in a To create a nondeterministic collation, specify the propertyĭeterministic = false to CREATE COLLATION, for example: CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false) Standard 10 for more information on the terminology. Up to the collation provider to actually implement such insensitiveĬomparisons the deterministic flag only determines whether ties are Well as comparison of strings in different Unicode normal forms. Include case-insensitive comparison, accent-insensitive comparison, as

Nondeterministic comparison may determine strings to beĮqual even if they consist of different bytes. That it considers strings to be equal only if they consist of the sameīyte sequence. Aĭeterministic collation uses deterministic comparisons, which means Reproducing relevant portion for completeness:Ī collation is either deterministic or nondeterministic. This is only for Postgres 12.ĭetails are described in docs here. Again, the trick is to convert whatever you’re searching for to uppercase or lowercase using the SQL upper and lower functions, and then make your search string match that case.This may be too late for the original poster, but for completeness, the way to achieve case insensitive behaviour from PostgreSQL is to set a non-deterministic collation. I hope these case insensitive SQL SELECT query examples are helpful. Here’s how you use the uppercase function with a SQL LIKE query: select * from users where upper(first_name) like '%AL%' Īnd here’s the same case insensitive SQL LIKE query using the SQL lowercase function: select * from users where lower(first_name) like '%al%' You can use the same technique to make your SQL LIKE queries case insensitive as well. This works with all SQL databases I’ve used, including Postgresql, MySQL, and SQL Server. Or this: select * from users where lower(first_name) = 'fred' Īs you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you’ve used. The SQL standard way to perform case insensitive queries is to use the SQL upper or lower functions, like this: select * from users where upper(first_name) = 'FRED' Case insensitive SQL SELECT: Use upper or lower functions Obviously this is a very important caveat you need to be aware of. So if you have a billion records in your table, this approach will force the database to look at all one billion records. This means that if you have an index on the column that you’re searching, that index won’t be used. These queries force your database to perform a table scan. Please note one VERY IMPORTANT thing about the approach shown: The queries shown should work with most, if not all, SQL 92 compliant databases. To save you some of that grief, the following examples show how to write case-insensitive SQL SELECT queries using SQL standard syntax. After some debugging I remembered that I had used Postgreql-specific query capabilities, so I had to re-write that portion of the code to work with MySQL. That seemed like a good idea at the time, but when I tried to move my application to a MySQL database, a portion of my application no longer worked. When I first started writing SQL queries I was using Postgresql, and used some of their custom regular expression capabilities to perform case-insensitive queries. SQL SELECT query FAQ: How do I issue SQL SELECT queries while ignoring case (i.e., case-insensitive queries, ignoring whether a string is uppercase or lowercase)? Background

Postgresql like ignore case