1.8.1 Using COLLATE in SQL Statements

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:

• With ORDER BY:

  Press CTRL+C to copy
   
  SELECT k
  FROM t1
  ORDER BY k COLLATE latin1_german2_ci;

• With AS:

  Press CTRL+C to copy
   
  SELECT k COLLATE latin1_german2_ci AS k1
  FROM t1
  ORDER BY k1;

• With GROUP BY:

  Press CTRL+C to copy
   
  SELECT k
  FROM t1
  GROUP BY k COLLATE latin1_german2_ci;

• With aggregate functions:

  Press CTRL+C to copy
   
  SELECT MAX(k COLLATE latin1_german2_ci)
  FROM t1;

• With DISTINCT:

  Press CTRL+C to copy
   
  SELECT DISTINCT k COLLATE latin1_german2_ci
  FROM t1;

• With WHERE:

  Press CTRL+C to copy
   
       SELECT *
       FROM t1
       WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

  Press CTRL+C to copy
   
       SELECT *
       FROM t1
       WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

• With HAVING:

  Press CTRL+C to copy
   
  SELECT k
  FROM t1
  GROUP BY k
  HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;