Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL UPPER() and LOWER() functions

Reference for SQL UPPER() and LOWER(): change the case of text, case-insensitive comparison, Unicode limits in SQLite, INITCAP and collation notes.

UPPER(s) converts all letters of a string to capitals and LOWER(s) converts them to small letters. They are used to display text consistently, to compare strings without caring about case, and to normalize values such as emails before storing them.

Characters that are not letters (digits, punctuation, spaces) pass through unchanged.

SQL
SELECT UPPER('sql') AS shouted, LOWER('SQL') AS quiet;

Try it

SQL Changing case ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Case-insensitive search and the ASCII-only limit

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
UPPER(string)
LOWER(string)
Name Description
string The text to convert.

Returns: the converted text. NULL if the input is NULL.

Dialect differences

Database Notes
SQLite UPPER/LOWER only change ASCII letters (a to z); รฉ and ร€ stay as they are, unless the ICU extension is loaded.
PostgreSQL Full Unicode, according to the database locale. Also has INITCAP() for title case.
MySQL Full Unicode, according to the character set and collation.
SQL Server Full Unicode for nvarchar; depends on the collation for varchar. No INITCAP.
Oracle Full Unicode, depending on the national language settings; has INITCAP().
SQL
-- PostgreSQL, Oracle: capitalize each word
SELECT INITCAP('hello big world');   -- Hello Big World

Usage notes

  • Case-insensitive comparison: WHERE LOWER(email) = LOWER(:input) works everywhere. It cannot use an ordinary index on email; create an index on LOWER(email) (PostgreSQL, Oracle, SQLite) or use a case-insensitive collation instead.
  • Many databases are already case-insensitive for text comparison by collation: MySQL and SQL Server by default, SQLite with COLLATE NOCASE, and PostgreSQL through the citext extension or a nondeterministic collation. Check before adding LOWER() everywhere.
  • LIKE is case-insensitive for ASCII letters in SQLite and in MySQL and SQL Server by default, but case-sensitive in PostgreSQL (use ILIKE). See LIKE.
  • Special cases exist in some languages: German รŸ becomes SS in some systems, and Turkish dotted/dotless i depends on locale.
  • Capitalize one word with UPPER(SUBSTR(s, 1, 1)) || LOWER(SUBSTR(s, 2)) in SQLite (use CONCAT in MySQL and + in SQL Server).
  • Normalize on write (store lowercase emails) rather than on every read.
  • NULL in, NULL out.