LOWER

Return the input string with every character converted to its lowercase equivalent.

Category: stringReturns: VARCHARDialect: Standard

Syntax

LOWER(str)

Description

## Overview Returns the input string with every uppercase character replaced by its lowercase equivalent. Non-letter characters (digits, punctuation, whitespace) are passed through unchanged. LOWER is the most common way to build case-insensitive keys for joins, GROUP BY clauses, and deduplication, and it is widely used to normalise email addresses, user handles, URLs, and tag strings. Because LOWER applies Unicode case mapping, it handles Latin accented letters, Greek, Cyrillic, and any other cased script. For locale-sensitive rules (for example the Turkish dotted/dotless I) LOWER does not apply Turkish-specific mappings; use a locale-aware transformation upstream if that matters. ## Behavior - Returns NULL when the input is NULL. - Returns an empty string when the input is an empty string. - Non-cased characters (digits, punctuation, whitespace, symbols) are passed through unchanged. - Operates on Unicode code points: multi-byte UTF-8 characters are handled correctly and the output is always valid UTF-8. - A few characters shrink on lowercasing, but output length in characters is usually the same as the input. - Does not modify surrogate pairs or combining marks; characters outside the Basic Multilingual Plane are preserved. - Idempotent: LOWER(LOWER(x)) equals LOWER(x). ## Compatibility - Conforms to the SQL standard definition of LOWER. - Case mapping follows the default (language-neutral) Unicode rules. Locale-sensitive variants are not applied automatically.

Parameters

Name	Type	Description
`str`		Specifies the input string to convert to lowercase. Accepts any UTF-8 string value.

Examples

-- Basic uppercase to lowercase
SELECT LOWER('HELLO');  -- 'hello'

-- Produce a case-insensitive email key
SELECT LOWER(TRIM(email)) AS email_key
FROM retail.customers.profiles;

-- NULL propagates
SELECT LOWER(CAST(NULL AS VARCHAR));  -- NULL

-- Empty string stays empty
SELECT LOWER('');  -- ''

-- Non-alphabetic characters are unchanged
SELECT LOWER('ABC-123!');  -- 'abc-123!'

-- Case-insensitive join using LOWER on both sides
SELECT o.order_id, c.full_name
FROM retail.sales.orders o
JOIN retail.customers.profiles c
  ON LOWER(o.customer_email) = LOWER(c.email);

Pitfalls

LOWER(col) = 'x' can prevent use of a plain index on col. Create a functional index on LOWER(col), or store a normalised shadow column if this predicate is hot.
LOWER does not strip accents. LOWER('CAFÉ') is 'café', not 'cafe'. Use TO_ASCII or NORMALIZE plus combining-mark removal for accent folding.
Turkish locale treats the dotless I specially. Default Unicode LOWER does not apply Turkish rules, so Turkish text may not round-trip as expected.
Do not rely on LOWER for security-sensitive normalisation of Unicode confusables (for example identifier spoofing). Use a dedicated confusable-detection pipeline.