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.
#MYSQL CASE SELECT 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. 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)
