Строковые функции

Работа с текстом в PostgreSQL

Теория

Часто в SQL нужно не просто взять текст из столбца, а каким-то образом его обработать. Где-то хочется показать имя заглавными буквами, где-то взять только первые символы, где-то вытащить домен из почты, а где-то проверить длину строки.

В PostgreSQL для этого существует достаточно много разных функций, которые позволяют обрабатывать по разному строки. Такая функция берёт текст, делает с ним нужное действие и возвращает новый результат.

Например:
• нужно показать имя пользователя в верхнем регистре;
• нужно взять только первые три символа из названия стран чтобы получить сокращенную аббревиатуру;
• нужно получить часть строки после знака '@';
• нужно заменить один кусок текста на другой;
• нужно убрать пробелы или лишние символы по краям строки.

Важно: строковые функции сами по себе не меняют данные в таблице если их использовать в обычных SELECT запросах. Они только меняют вид результата в запросе. То есть в базе значение остаётся как было, а в выдаче вы можете показать его уже в нужном виде.

Такие функции хорошо работают не только в операторе SELECT. Их часто используют и в WHERE, и в ORDER BY, и даже внутри других функций.

Синтаксис

SELECT UPPER(first_name),
       LEFT(product_name, 5),
       SPLIT_PART(email, '@', 2)
FROM table_name;

SELECT SUBSTRING(product_name, 4, 8)
FROM table_name;

SQL

Компактная шпаргалка по самым полезным строковым функциям.

Пример	Результат	Что делает
UPPER('sql')	'SQL'	Переводит текст в верхний регистр.
LOWER('SQL')	'sql'	Переводит текст в нижний регистр.
LEFT('PostgreSQL', 4)	'Post'	Берёт указанное количество символов с начала строки, то есть слева.
RIGHT('PostgreSQL', 3)	'SQL'	Берёт указанное количество символов с конца строки, то есть справа.
SUBSTRING('PostgreSQL', 5, 6)	'greSQL'	Вырезает фрагмент строки с нужной позиции.
SPLIT_PART('user@gmail.com', '@', 2)	'gmail.com'	Делит строку по разделителю и возвращает нужную часть.
LENGTH('report')	6	Показывает длину строки.
REPLACE('user@gmail.com', 'gmail.com', 'example.com')	'user@example.com'	Заменяет один фрагмент текста на другой.
TRIM(' SQL ')	'SQL'	Убирает пробелы по краям строки.
LTRIM('000123', '0')	'123'	Убирает указанные символы слева.
RTRIM('report---', '-')	'report'	Убирает указанные символы справа.
INITCAP('ivan ivanov')	'Ivan Ivanov'	Делает первые буквы слов заглавными.
POSITION('@' IN 'user@gmail.com')	5	Показывает, с какой позиции начинается нужный фрагмент.

Теперь разберём некоторые нюансы.

1. После имени функции в скобках передаётся значение, с которым она будет работать.
Например, в UPPER(first_name) функция берёт значение из столбца first_name и переводит его в верхний регистр.

2. В LEFT и RIGHT второе число — это количество символов.
LEFT(product_name, 5) возьмёт первые пять символов, а RIGHT(product_name, 5) — последние пять.

3. У SUBSTRING в PostgreSQL есть две рабочие формы записи.
Можно написать коротко: SUBSTRING(product_name, 4, 8). А можно длиннее: SUBSTRING(product_name FROM 4 FOR 8). Смысл у них один и тот же: извлечь 8 символов из строки начиная с 4 символа (конкретно для данного примера).

4. В SPLIT_PART номер части начинается с 1.
Если написать SPLIT_PART(email, '@', 1), вернётся часть до '@'. Если написать SPLIT_PART(email, '@', 2), вернётся часть после него.

5. TRIM, LTRIM и RTRIM умеют работать не только с пробелами.
Если передать второй аргумент, можно убирать конкретные символы с начала или конца строки. Например: LTRIM('000123', '0') уберет все попавшиеся 0 с начала строки.

6. Функции можно вкладывать друг в друга.
Да, так бывает и бывает часто. Например, в выражении UPPER(LEFT(city, 3)) сначала отрабатывает LEFT и выведет первые три символа, а потом UPPER переведет эти три символа в верхний регистр.

7. Результат функции лучше сразу называть через псевдоним.
Например: UPPER(first_name) AS first_name_upper или SPLIT_PART(email, '@', 2) AS email_domain. Так результат заметно удобнее читать.

8. Если в функцию попадает NULL, результат тоже часто будет NULL.
Это нормальное поведение. Если нужно заранее подставить запасной вариант, рядом часто используют COALESCE.

Примеры

1. Меняем регистр и делаем текст аккуратнее

SELECT user_id,
       first_name,
       city,
       UPPER(first_name) AS first_name_upper,
       LOWER(city) AS city_lower,
       INITCAP(first_name) AS first_name_title
FROM users
ORDER BY registration_date DESC;

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

🔄 Попробуйте изменить запрос:

• Замените UPPER(first_name) на LOWER(first_name) и сравните результат

2. Берём начало и конец названия товара

SELECT product_id,
       product_name,
       LEFT(product_name, 8) AS start_part,
       RIGHT(product_name, 6) AS end_part
FROM products
ORDER BY product_id;

Функции LEFT и RIGHT удобны, когда нужно быстро взять начало или конец строки. Это может пригодиться и для разбора данных, и для проверки формата текста.

🔄 Попробуйте изменить запрос:

• Поменяйте число 8 на 5 и посмотрите, как изменится начало строки
• Сделайте конец строки короче: замените 6 на 3

3. Вкладываем одну функцию в другую

SELECT user_id,
       first_name,
       city,
       UPPER(LEFT(city, 3)) AS city_prefix_upper
FROM users
WHERE city IS NOT NULL
ORDER BY registration_date DESC;

Здесь показана идея вложенных функций. Сначала LEFT(city, 3) берёт первые три символа города, а потом UPPER(...) переводит этот фрагмент в верхний регистр. Такой приём в SQL используется очень часто.

🔄 Попробуйте изменить запрос:

• Попробуйте поменять порядок и сделать LEFT(UPPER(city), 3) — результат будет тем же, но структура выражения станет другой

4. Комбинируем вложенность и разбиение строки

SELECT user_id,
       first_name,
       email,
       UPPER(SPLIT_PART(email, '@', 2)) AS email_domain_upper
FROM users
ORDER BY registration_date DESC;

Здесь тоже используется вложенность функций, но уже в другом сценарии. Сначала SPLIT_PART достаёт домен из почты, а потом UPPER переводит его в верхний регистр.

🔄 Попробуйте изменить запрос:

• Замените внешнюю функцию UPPER на LOWER и сравните результат
• Поменяйте номер части в SPLIT_PART с 2 на 1

5. Вырезаем фрагмент не с начала, а из середины строки

SELECT product_id,
       product_name,
       SUBSTRING(product_name, 4, 8) AS middle_part
FROM products
WHERE price >= 50000
ORDER BY price DESC;

SUBSTRING берёт кусок строки не с начала, а с четвёртого символа. Это важно: функция умеет вырезать не только начало текста. Здесь показана короткая форма записи SUBSTRING(product_name, 4, 8). В PostgreSQL точно так же можно написать и длинную форму: SUBSTRING(product_name FROM 4 FOR 8).

🔄 Попробуйте изменить запрос:

• Поменяйте начало фрагмента с 4 на 1 и сравните результат
• Перепишите тот же запрос в длинной форме: SUBSTRING(product_name FROM 4 FOR 8)

6. Достаём домен из электронной почты

SELECT user_id,
       first_name,
       email,
       SPLIT_PART(email, '@', 2) AS email_domain
FROM users
WHERE city IS NOT NULL
ORDER BY email_domain ASC, registration_date DESC;

Функция SPLIT_PART делит строку по разделителю. Здесь разделитель — знак '@'. Первая часть — имя почтового ящика, вторая — домен. Мы как раз берём вторую часть.

🔄 Попробуйте изменить запрос:

• Замените номер части 2 на 1 и посмотрите, что вернётся теперь
• Добавьте фильтр только на домен 'gmail.com' через WHERE SPLIT_PART(email, '@', 2) = 'gmail.com'

7. Ищем позицию фрагмента и делаем замену части текста

SELECT user_id,
       first_name,
       email,
       POSITION('@' IN email) AS at_position,
       REPLACE(email, 'gmail.com', 'example.com') AS demo_email
FROM users
ORDER BY registration_date DESC;

POSITION говорит, с какого символа начинается нужный фрагмент внутри строки. А REPLACE заменяет один кусок текста на другой.

🔄 Попробуйте изменить запрос:

• Замените искомый фрагмент '@' на '.' и посмотрите, что вернёт POSITION
• Замените в REPLACE домен 'gmail.com' на 'mail.ru'

8. Убираем пробелы и лишние символы по краям строки

SELECT TRIM('   SQL   ') AS trim_spaces,
       LTRIM('000123', '0') AS ltrim_zeroes,
       RTRIM('report---', '-') AS rtrim_dashes;

Этот пример запустим без обращении к таблице. TRIM убирает пробелы по краям строки. LTRIM убирает указанные символы слева, а RTRIM — справа. Если не указать в последних двух функциях второй аргумент после запятой, то они просто уберут пробелы слева и справа соответственно, если они там есть.

🔄 Попробуйте изменить запрос:

• Уберите у LTRIM второй аргумент и посмотрите, что функция вернет
• Замените символ '-' в RTRIM на другой и проверьте, как изменится результат

9. Смотрим длину названий должностей работников

SELECT worker_id,
       first_name,
       position,
       LENGTH(position) AS position_length
FROM workers
WHERE department_id IS NOT NULL
ORDER BY position_length DESC, first_name ASC;

Функция LENGTH возвращает длину текста. Это полезно, когда нужно сравнить строки по размеру, найти слишком короткие или слишком длинные значения или просто проверить структуру данных.

🔄 Попробуйте изменить запрос:

• Отсортируйте результат по возрастанию длины: замените DESC на ASC
• Добавьте в выборку LENGTH(first_name) AS first_name_length и используя WHERE оставьте в результате только тех сотрудников, чье имя короче 6 букв

Типичные ошибки

Думают, что функция меняет данные в таблице
Нет, не меняет. Запись вроде UPPER(first_name) только меняет вид результата в запросе. В самой таблице текст остаётся как был. Речь конечно идет про обычные SELECT запросы.

Путают, что означает число в LEFT, RIGHT и SUBSTRING
В LEFT(text, 5) и RIGHT(text, 5) число — это количество символов. А в SUBSTRING(text FROM 3 FOR 5) сначала идёт стартовая позиция, а потом длина фрагмента.

Забывают, что в SPLIT_PART номер части начинается с 1
SPLIT_PART(email, '@', 1) вернёт часть до '@', а SPLIT_PART(email, '@', 2) — часть после него.

Думают, что TRIM, LTRIM и RTRIM убирают пробелы везде внутри строки
Нет, эти функции работают именно с краями строки. Внутренние пробелы они не трогают.

Забывают, что LTRIM и RTRIM умеют убирать не только пробелы
Если передать второй аргумент, можно срезать конкретные символы с начала или конца строки. Например: LTRIM('000123', '0') или RTRIM('report---', '-').

Не учитывают поведение NULL
Если в функцию попадает NULL, результат тоже часто будет NULL. Если нужно заранее подставить запасной вариант, рядом часто используют COALESCE.

Оставляют вычисляемый столбец без понятного имени
Когда в запросе много функций, результат без псевдонимов быстро становится нечитаемым. Лучше сразу писать что-то вроде LOWER(email) AS email_lower или LENGTH(position) AS position_length.

Строковые функции

Теория

Синтаксис

Примеры

1. Меняем регистр и делаем текст аккуратнее

🔄 Попробуйте изменить запрос:

2. Берём начало и конец названия товара

🔄 Попробуйте изменить запрос:

3. Вкладываем одну функцию в другую

🔄 Попробуйте изменить запрос:

4. Комбинируем вложенность и разбиение строки

🔄 Попробуйте изменить запрос:

5. Вырезаем фрагмент не с начала, а из середины строки

🔄 Попробуйте изменить запрос:

6. Достаём домен из электронной почты

🔄 Попробуйте изменить запрос:

7. Ищем позицию фрагмента и делаем замену части текста

🔄 Попробуйте изменить запрос:

8. Убираем пробелы и лишние символы по краям строки

🔄 Попробуйте изменить запрос:

9. Смотрим длину названий должностей работников

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя