This works with all SQL databases I’ve used, including Postgresql, MySQL, and SQL Server. 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. Select * from users where upper(first_name) = 'FRED' The SQL standard way to perform case insensitive queries is to use the SQL upper or lower functions, like this: I don’t post comments from people who use derogatory language, but as one person wrote in the comments (between insults), “using a function to convert an entire column of data requires a table scan and should be avoided in a WHERE clause.” 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 that there is one VERY IMPORTANT thing to say about the approach shown: The queries shown should work with most, if not all, SQL 92 compliant databases.
SQLITE ORDER BY IGNORECASE HOW TO
To save you some of that grief, the following examples show how to write case-insensitive SQL SELECT queries using SQL standard syntax.
SQLITE ORDER BY IGNORECASE CODE
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
0 kommentar(er)
