|
Custom Search
| |
|
How to make case-sensitive comparison in MySQL String comparison is NOT case-sensitive by default in MySQL, but if there is a need for case-sensitive string comparison, there are ways to deal with it. 1. Use BINARY operator The BINARY operator converts a character string to a binary string. It forces a character string comparison to be done byte by byte using numeric byte values rather than character by character. For example, the following query returns 1 which stands for TRUE because the string geek is the same as string GEEK.
SELECT 'geek' = 'GEEK'; But if we apply BINARY operator to the string 'GEEK', the result is FALSE (returns 0) because the string lowercase geek is different to uppercase string GEEK.
SELECT 'geek' = BINARY 'GEEK'; Another example: select 'géek' = 'geek'; Query above returns 1 which is TRUE, whereas select 'géek' = BINARY 'geek';returns 0 which is FALSE. The following query is case-sensitive search against a column by using BINARY operator. It extracts all category records where the category name contains lowercase character 's'. Note that category 'Seafood' is not in the result because the first letter 'S' is uppercase.
select CategoryID, CategoryName from categories where BINARY CategoryName like '%s%';
5 records returned: Below is the case-insensitive version of the above query and it extracts all category records where the category name contains character 's' or 'S' (both lowercase 's' and uppercase 'S' are returned in the result).
select CategoryID, CategoryName from categories where CategoryName like '%s%';
6 records returned: A couple of things to note about using BINARY operator:
2. Use the COLLATE operator to define a column as case-sensitive Below a new table is created and column CategoryName is defined by COLLATE utf8mb4_bin which is case-sensitive. CREATE TABLE categories_case_sensitive ( `CategoryID` tinyint unsigned NOT NULL, `CategoryName` varchar(15) COLLATE utf8mb4_bin NOT NULL DEFAULT '' ) ENGINE=InnoDB; Now add some records to the new table. insert into categories_case_sensitive(CategoryID,CategoryName) values(1,'Beverages'); insert into categories_case_sensitive(CategoryID,CategoryName) values(2,'Condiments'); insert into categories_case_sensitive(CategoryID,CategoryName) values(3,'Confections'); insert into categories_case_sensitive(CategoryID,CategoryName) values(4,'Dairyé Products'); insert into categories_case_sensitive(CategoryID,CategoryName) values(5,'Grains/Cereals'); insert into categories_case_sensitive(CategoryID,CategoryName) values(6,'Meat/Poultry'); insert into categories_case_sensitive(CategoryID,CategoryName) values(7,'Produce'); insert into categories_case_sensitive(CategoryID,CategoryName) values(8,'Seafood'); Query the new table as normal (BINARY operator is not needed and the search on CategoryName column is case-sensitive by default). select * from categories_case_sensitive where CategoryName like '%s%';
5 records returned:
Happy Coding! Other tutorials in this category 1. Using String Functions, Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |