Question 21378 in Mysql Question posted in
The official MySQL documentation can be found here.

Applying UPPER Operator on an Array of string – Mysql

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    id val
    1 aBC
    2 def
    3 ghI 
    SELECT * FROM test WHERE val IN ('abc', 'def');
    id val
    2 def 
    SELECT * FROM test WHERE val COLLATE utf8mb4_0900_ai_ci IN ('abc', 'def');
    id val
    1 aBC
    2 def

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search