Case Sensitive String Occurrence

Question

I am using the following query to split column values. It's working for case insensitive scenario, but I want it to work with case sensitivity.

For example, in the string 'Oil is a product Ingredients are' if my searching keyword is 'ingredients' it should return false and should only return true if searching keyword is 'Ingredients'. Is there any function in mysql which allows this?

SELECT 
  SUBSTRING_INDEX(description, 'Ingredients', 1),
    if(LOCATE('Ingredients', description)>0, SUBSTRING_INDEX(description, 'Ingredients', -1), '')
FROM `product`

Possible duplicate of [How can I make SQL case sensitive string comparison on MySQL?](https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql) — Paul Spiegel, Mar 19 '18 at 21:21

score 1 · Accepted Answer · answered Mar 19 '18 at 20:40

From the documentation of mysql's LOCATE function:

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

That is, you need to cast/convert your arguments to perform a case-sensitive match.

For example: If your first record is Oil is a product Ingredients are... and your second record is Oil is a product ingredients are... then the following query:

SELECT 
  LOCATE('ingredients', description) AS match_both_1,
  LOCATE('Ingredients', description) AS match_both_2,
  LOCATE(CAST('ingredients' AS BINARY), CAST(description AS BINARY)) AS match_second,
  LOCATE(CAST('Ingredients' AS BINARY), CAST(description AS BINARY)) AS match_first
FROM product

will give you the expected results:

| match_both_1 | match_both_2 | match_second | match_first |
|     18       |     18       |      0       |     18      |
|     18       |     18       |     18       |      0      |

See DEMO.

Case Sensitive String Occurrence

1 Answers1