Что такое LOWER, UPPER, LENGTH в SQL? Строковые функции для начинающих

Это три самые базовые строковые функции. LOWER переводит текст в нижний регистр, UPPER — в верхний, LENGTH — считает длину. Звучит просто, но именно с них начинается работа с реальными данными: нормализация email, очистка от пробелов, поиск без учёта регистра.

В этой статье разберём не только эти три, но и набор «соседей»: TRIM, SUBSTRING, REPLACE — без них в реальных задачах тоже не обойтись.

Зачем нужны строковые функции

Реальные данные грязные. Email пишут с большой буквы, с пробелом в конце, иногда с табом. Имена — с лишними пробелами после копипасты. Номера телефонов — то с дефисами, то без. Строковые функции — это инструмент нормализации: привести разные представления одного и того же значения к единой форме.

LOWER и UPPER

SELECT LOWER('Hello World');     -- 'hello world'
SELECT UPPER('Hello World');     -- 'HELLO WORLD'

Главный практический сценарий — case-insensitive сравнение:

-- Найти юзера по email вне зависимости от регистра
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');

Тут есть подводный камень: функция в WHERE мешает обычному индексу на колонке. Postgres не сможет использовать индекс на email, придётся делать full scan.

Решения:

1. Functional index: CREATE INDEX users_lower_email ON users(LOWER(email)); — Postgres использует.
2. Хранить email сразу в нижнем регистре (нормализовать при INSERT/UPDATE).
3. В Postgres есть тип citext — case-insensitive text «из коробки».

Для русского/Юникода тоже работает:

SELECT LOWER('ПРИВЕТ');  -- 'привет'

LENGTH — считаем символы (с нюансом)

-- PostgreSQL
SELECT LENGTH('Hello');      -- 5
SELECT LENGTH('Привет');     -- 6 (символов)
SELECT OCTET_LENGTH('Привет'); -- 12 (байтов в UTF-8)

В Postgres LENGTH возвращает символы. В UTF-8 русская буква занимает 2 байта, поэтому OCTET_LENGTH показывает 12.

В MySQL поведение другое:

-- MySQL
SELECT CHAR_LENGTH('Привет'); -- 6 (символов)
SELECT LENGTH('Привет');      -- 12 (БАЙТОВ!)

В MySQL LENGTH — это байты, а для символов — CHAR_LENGTH. Это частая ошибка при кросс-БД миграциях: «у нас валидация max 30, но в MySQL она допускает 30 байт = 10 китайских иероглифов».

Стандарт SQL: CHARACTER_LENGTH (символы) и OCTET_LENGTH (байты). Если важна однозначность — используй их.

TRIM — убираем пробелы

SELECT TRIM('   hello   ');        -- 'hello'
SELECT LTRIM('   hello');           -- 'hello' (только слева)
SELECT RTRIM('hello   ');           -- 'hello' (только справа)

-- Обрезка конкретного символа
SELECT TRIM('x' FROM 'xxxhelloxxx'); -- 'hello'

Классический use case — нормализация после CSV-импорта или копипасты:

UPDATE users SET email = LOWER(TRIM(email));

Юзеры постоянно привозят пробелы и переводы строк в начале/конце. TRIM чистит.

SUBSTRING — извлечь кусок строки

SELECT SUBSTRING('Hello World' FROM 1 FOR 5);  -- 'Hello'
SELECT SUBSTRING('Hello World' FROM 7);        -- 'World' (от 7-й позиции до конца)

-- MySQL-style синтаксис, Postgres тоже принимает
SELECT SUBSTRING('Hello World', 1, 5);         -- 'Hello'

Индексация с 1, не с 0 — типичная для SQL.

С регулярными выражениями (Postgres):

-- Извлечь домен из email
SELECT SUBSTRING('alice@example.com' FROM '@(.+)$');  -- 'example.com'

REPLACE — замена

SELECT REPLACE('Hello World', 'World', 'SQL');   -- 'Hello SQL'
SELECT REPLACE('aaaa', 'a', 'bb');               -- 'bbbbbbbb' (не aabbaa!)

REPLACE заменяет все вхождения, не только первое.

Use case — массовая нормализация:

-- Привести все телефоны к формату без пробелов и тире
UPDATE users SET phone = REPLACE(REPLACE(phone, ' ', ''), '-', '');

Двойной REPLACE — потому что обе подстроки нужно убрать.

Конкатенация — || и CONCAT

Подробнее в отдельной статье. Кратко:

-- Стандарт SQL и Postgres
SELECT 'Hello, ' || 'Alice';  -- 'Hello, Alice'

-- Универсально (PG, MySQL, MSSQL)
SELECT CONCAT('Hello, ', 'Alice');

Разница в обработке NULL:

• ||: 'Hello' || NULL → NULL (вся строка стала NULL).
• CONCAT: CONCAT('Hello', NULL) → 'Hello' (NULL игнорируется).

Пример с таблицей

users:

id	email	name
1	Alice@Example.com	Аня
2	bob@example.com	Боб
3	vera@EXAMPLE.com	Вера

После UPDATE users SET email = LOWER(TRIM(email)), name = TRIM(name):

id	email	name
1	alice@example.com	Аня
2	bob@example.com	Боб
3	vera@example.com	Вера

Все email-ы в нижнем регистре, без пробелов. Имена — без пробелов.

Частые ошибки новичков

1. Функция в WHERE мешает индексу. WHERE LOWER(email) = '...' без functional index — full scan. Если поиск часто — либо functional index, либо citext, либо нормализуй данные при write.

2. LENGTH в MySQL ≠ LENGTH в Postgres. В Postgres = символы, в MySQL = байты. При миграции — обязательно проверять. Универсальный — CHAR_LENGTH.

3. TRIM по умолчанию обрезает только пробелы (U+0020). Не-разрывный пробел (U+00A0), табы, переводы строк — TRIM их не трогает. Для полной зачистки — TRIM(E' \t\n\r' FROM x).

4. NULL в строковых операциях. LOWER(NULL) = NULL, LENGTH(NULL) = NULL, 'a' || NULL = NULL. Всё «протекает» NULL'ом наружу. В сравнениях защищайся через COALESCE(x, '').

5. UPPER/LOWER не идемпотентны для всех языков. В немецком UPPER('ß') → 'SS', и LOWER('SS') → 'ss'. То есть LOWER(UPPER(x)) ≠ x. На русских данных проблем обычно нет, но проверяй на своей локали.

6. SUBSTRING с отрицательным смещением. В Postgres SUBSTRING('hello', -2) начнёт с позиции 1 (не с конца, как в Python). Чтобы взять последние N — RIGHT(str, N).

Мини-резюме

• LOWER / UPPER — нижний / верхний регистр.
• LENGTH — длина (в Postgres — символы, в MySQL — байты, осторожно при миграции).
• TRIM — обрезка пробелов (по умолчанию только обычный ' ').
• SUBSTRING — извлечь подстроку с позиции, индексация с 1.
• REPLACE — заменить все вхождения подстроки.
• Функция в WHERE мешает индексу — для частого поиска используй functional index, citext или нормализацию при write.
• NULL «протекает» через все строковые функции — защищайся COALESCE(x, '').