How can I apply UPPER on an array of string in MySQL

E.g. ... WHERE col1 IN UPPER('abc', 'def') i.e., I want to match a column value against an array of strings, after applying UPPER or any other MySQL string function on each value in the array

CodePudding user response：

UPPER() only takes one argument. You could use it for each value like this:

... WHERE col1 IN (UPPER('abc'), UPPER('def'), ...)

But it's easier to use a case-insensitive collation, so you don't have to convert the strings to uppercase. MySQL's default collation is already case-insensitive, so you may not have to do anything. If you have defined the col1 column with a case-sensitive collation, then you would need to change it.

CodePudding user response：

Using UPPER/LOWER functions while comparing the strings is not correct. You must specify needed collation instead.

DEMO

CREATE TABLE test (
  id INT,
  val CHAR(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin
);
INSERT INTO test VALUES (1, 'aBC'), (2, 'def'), (3, 'ghI');
SELECT * FROM test;

SELECT * FROM test WHERE val IN ('abc', 'def');

SELECT * FROM test WHERE val COLLATE utf8mb4_0900_ai_ci IN ('abc', 'def');

fiddle