Menu
I looked around some and didn't find what I was after so here goes.
SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM etc...
How do I make SQL case insensitive for this wild-card search?
I'm using MySQL 5 and Apache.
624
LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive. For case-sensitive matches, declare either argument to use a binary collation using COLLATE , or coerce either of them to a BINARY string using CAST .
Case insensitive SQL SELECT: Use upper or lower functions or this: select * from users where lower(first_name) = 'fred'; As 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.
Using LOWER( ad UPPER() functions for case sensitive queries In the similar fashion UPPER() and LOWER() functions can be used in the LIKE clause for getting similar records and making the search insensitive in the table or database having collation that shows CS that is case sensitive in its collation.
select * from users where lower(first_name) = 'ajay'; The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.
I've always solved this using lower:
SELECT * FROM trees WHERE LOWER( trees.title ) LIKE '%elm%' 
SELECT * FROM trees WHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%' Actually, if you add COLLATE UTF8_GENERAL_CI to your column's definition, you can just omit all these tricks: it will work automatically.
ALTER TABLE trees MODIFY COLUMN title VARCHAR(…) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI. This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
