Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 296.6Kb
Man Pages (Zip) - 401.9Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Using COLLATE in SQL Statements

12.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;