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.
SELECT UPPER('sql') AS shouted, LOWER('SQL') AS quiet;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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(). |
-- 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 onemail; create an index onLOWER(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 thecitextextension or a nondeterministic collation. Check before addingLOWER()everywhere. LIKEis case-insensitive for ASCII letters in SQLite and in MySQL and SQL Server by default, but case-sensitive in PostgreSQL (useILIKE). SeeLIKE.- Special cases exist in some languages: German
รbecomesSSin 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 (useCONCATin MySQL and+in SQL Server). - Normalize on write (store lowercase emails) rather than on every read.
NULLin,NULLout.
Related
LENGTH(),SUBSTR(),TRIM(),||concatenation,LIKE- Lessons: String functions, LIKE