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

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

Теория

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

Предположим, что перед отображением текстовой строки в результате необходимо выполнить её обработку: преобразовать имя в верхний регистр, извлечь первые символы, выделить домен из электронной почты или проверить длину строки.

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

Например:

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

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

Синтаксис

SELECT UPPER(first_name),          -- переводим текст в верхний регистр
       LEFT(product_name, 5),      -- берём первые 5 символов из названия товара
       SPLIT_PART(email, '@', 2)   -- делим email по символу @ и берём вторую часть
FROM table_name;                   -- указываем таблицу, из которой берём данные

SELECT SUBSTRING(product_name, 4, 8) -- берём 8 символов из product_name, начиная с 4-го
FROM table_name;                     -- указываем таблицу, из которой берём данные

SQL

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

Пример	Результат	Что делает
UPPER('sql')	'SQL'	Переводит текст в верхний регистр.
LOWER('SQL')	'sql'	Переводит текст в нижний регистр.
LEFT('PostgreSQL', 4)	'Post'	Берёт указанное количество символов с начала строки, то есть слева.
RIGHT('PostgreSQL', 3)	'SQL'	Берёт указанное количество символов с конца строки, то есть справа.
SUBSTRING('PostgreSQL', 5, 6)	'greSQL'	Вырезает фрагмент строки с нужной позиции. Начиная с 5 символа вырезает следующие 6.
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(...) преобразует этот фрагмент в верхний регистр.

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

• Попробуйте поменять порядок и сделать 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 на INITCAP и сравните результат
• Поменяйте номер части в 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. Оставляют вычисляемый столбец без понятного имени Когда в запросе много функций, результат без псевдонимов становится нечитаемым.

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

Теория

Синтаксис

Примеры

1. Меняем регистр строк

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Практика

Проверь себя