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

Строковые функции помогают объединять, очищать, разбивать и преобразовывать текст в SQL.

Регистр и длина

Функция	Назначение	Пример выражения SQL	Результат
`LOWER`	Нижний регистр	`lower('PostgreSQL')`	'postgresql'
`UPPER`	Верхний регистр	`upper('PostgreSQL')`	'POSTGRESQL'
`INITCAP`	Заглавная первая буква слов	`initcap('hello SQL')`	'Hello Sql'
`LENGTH`	Число символов	`length('Привет')`	6
`CHAR_LENGTH`	Число символов	`char_length('Привет')`	6
`OCTET_LENGTH`	Число байтов	`octet_length('Привет')`	12 в UTF-8

Преобразование регистра зависит от локали и правил сравнения текста. INITCAP делает остальные буквы слова строчными, поэтому не подходит для сохранения аббревиатур. Для текста LENGTH и CHAR_LENGTH считают символы, OCTET_LENGTH — байты.

Объединение строк

Функция	Назначение	Пример выражения SQL	Результат
`CONCAT`	Объединение значений	`concat('SQL', ' ', NULL, 'курс')`	'SQL курс'
`CONCAT_WS`	Объединение с разделителем	`concat_ws(', ', 'SQL', NULL, 'Python')`	'SQL, Python'
`Оператор \|\|`	Соединение строк	`'SQL' \|\| ' ' \|\| 'курс'`	'SQL курс'

CONCAT и CONCAT_WS пропускают аргументы NULL. Оператор || при наличии NULL возвращает NULL: 'SQL' || NULL. Если сам разделитель CONCAT_WS равен NULL, результат тоже NULL. Пустые строки функция не пропускает.

Извлечение и поиск

Функция	Назначение	Пример выражения SQL	Результат
`LEFT`	Первые n символов	`left('PostgreSQL', 4)`	'Post'
`RIGHT`	Последние n символов	`right('PostgreSQL', 3)`	'SQL'
`SUBSTRING`	Фрагмент по позиции	`substring('PostgreSQL' from 5 for 3)`	'gre'
`POSITION`	Первая позиция фрагмента	`position('SQL' in 'PostgreSQL')`	8
`SPLIT_PART`	Часть по разделителю	`split_part('a\|b\|c', '\|', 2)`	'b'
`STRING_TO_ARRAY`	Разбиение в массив	`string_to_array('a,b,c', ',')`	{a,b,c}

Позиции и номера частей отсчитываются с 1. POSITION возвращает 0, если фрагмент не найден. В SUBSTRING можно опустить длину: тогда извлекается весь остаток строки. Варианты substring(text, start, count) и substr(text, start, count) выполняют то же извлечение.

При отрицательном n LEFT возвращает строку без последних |n| символов, RIGHT — без первых |n|. Например, right('ABCDE', -2) даёт 'CDE'. SPLIT_PART возвращает пустую строку, если запрошенной части нет; отрицательный номер считает части с конца, номер 0 недопустим.

Удаление символов по краям

Функция	Назначение	Пример выражения SQL	Результат
`TRIM`	С обоих краёв	`trim(' SQL ')`	'SQL'
`BTRIM`	С обоих краёв	`btrim('xxSQLxy', 'xy')`	'SQL'
`LTRIM`	Слева	`ltrim('00123', '0')`	'123'
`RTRIM`	Справа	`rtrim('SQLxyyx', 'xy')`	'SQL'

По умолчанию эти функции удаляют обычные пробелы. Если задан второй аргумент, он означает набор отдельных символов, а не цельную подстроку. Удаление идёт до первого символа, который не входит в набор. Символы внутри строки сохраняются.

Например, ltrim('00123') оставит '00123': нули нужно явно указать. Для удаления только слева или справа у TRIM также есть варианты LEADING и TRAILING: trim(leading '0' from '00123').

Замена и преобразование

Функция	Назначение	Пример выражения SQL	Результат
`REPLACE`	Замена всех подстрок	`replace('a-b-c', '-', '/')`	'a/b/c'
`REPEAT`	Повтор строки	`repeat('SQL', 3)`	'SQLSQLSQL'
`REVERSE`	Обратный порядок символов	`reverse('ABC')`	'CBA'
`LPAD`	Дополнение слева	`lpad('123', 5, '0')`	'00123'
`RPAD`	Дополнение справа	`rpad('ABC', 6, 'xo')`	'ABCxox'

У LPAD и RPAD второй аргумент задаёт итоговую длину строки. Заполнитель повторяется и при необходимости обрезается. Если исходная строка длиннее указанного размера, обе функции обрежут её справа: lpad('12345', 3, '0') вернёт '123'.

Форматирование по шаблону

FORMAT подставляет значения в строковый шаблон. Для обычного текста используется %s; для SQL-идентификаторов — %I; для SQL-литералов — %L. Чтобы получить знак процента, укажите %%.

Функция	Назначение	Пример выражения SQL	Результат
`%s`	Текстовое значение	`format('Курс: %s', 'SQL')`	'Курс: SQL'
`%I`	SQL-идентификатор	`format('%I', 'user name')`	"user name"
`%L`	SQL-литерал	`format('%L', 'SQL')`	'SQL'

В последних двух примерах кавычки являются частью сформированной строки. %I добавляет двойные кавычки, когда они нужны идентификатору; %L оформляет значение как литерал и экранирует кавычки.

SELECT format('SELECT %I FROM %I WHERE name = %L', 'name', 'students', 'Anna');

Результат:
SELECT name FROM students WHERE name = 'Anna'.

FORMAT создаёт текст запроса, но не выполняет его. Для NULL спецификатор %s даёт пустую строку, %L — текст NULL без кавычек, а %I вызывает ошибку.

Регулярные выражения

Регулярное выражение описывает шаблон поиска. REGEXP_REPLACE заменяет найденные фрагменты, REGEXP_MATCH возвращает первое совпадение, а REGEXP_MATCHES может вернуть несколько строк с совпадениями.

Замена по шаблону

SELECT regexp_replace('A1 B22 C333', '[0-9]+', '#', 'g');

-- Результат:
A# B# C#

Флаг g заменяет все совпадения; без него в этой форме вызова заменяется только первое. Флаг i включает поиск без учёта регистра. В примере [0-9]+ означает одну или несколько цифр.

Извлечение совпадений

SELECT regexp_match('A12 B34', '([A-Z])([0-9]+)');

-- Результат:
{A,12}

SELECT regexp_matches('A12 B34', '([A-Z])([0-9]+)', 'g');

-- Две строки результата:
{A,12} и {B,34}

Скобки создают группы захвата: каждое совпадение возвращается массивом текстовых значений групп. Если групп нет, массив содержит всё совпадение. REGEXP_MATCH при отсутствии совпадений возвращает NULL, REGEXP_MATCHES — ни одной строки. Без g REGEXP_MATCHES возвращает только первое совпадение.

Коды символов и хеш

Функция	Назначение	Пример выражения SQL	Результат
ASCII	Код первого символа	ascii('ABC')	65
CHR	Символ по коду	chr(65)	'A'

В базе с кодировкой UTF-8 ASCII возвращает кодовую точку Unicode первого символа, а CHR выполняет обратное преобразование. Например, ascii('Я') даёт 1071, chr(1071) — «Я». Символ с кодом 0 в текстовых значениях PostgreSQL недопустим.

MD5 возвращает хеш строки как 32 шестнадцатеричных символа:

SELECT md5('ABC');

-- Результат:
902fbdd2b1df0c4f70b4a5d23525e932

MD5 — хеширование, а не шифрование. Оно не подходит для хранения паролей и задач, требующих устойчивости к подделке.

Практические примеры

Удалить пробелы и привести текст к нижнему регистру

SELECT lower(trim(' Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. '));

-- Результат:
Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.

Извлечь домен из простой строки адреса

SELECT split_part(Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '@', 2);

-- Результат:
example.com

Этот пример предполагает одну разделительную @ и не проверяет корректность адреса электронной почты.

Собрать имя с пропуском отсутствующей части

SELECT concat_ws(' ', 'Иван', NULL, 'Петров');

-- Результат:
Иван Петров

Очистить строку от лишних пробельных символов

SELECT trim(regexp_replace(
' Иван Петров ', '[[:space:]]+', ' ', 'g'
));

-- Результат:
Иван Петров

Сначала REGEXP_REPLACE сводит последовательности пробельных символов к одному обычному пробелу, затем TRIM убирает пробелы по краям.

Что учитывать при работе со строками

Строковые литералы заключают в одинарные кавычки. Апостроф внутри строки удваивают: 'O''Brien'. Большинство описанных функций возвращает NULL при аргументе NULL; исключения для CONCAT, CONCAT_WS и FORMAT указаны выше.

Чтобы заменить отсутствующее значение, используйте coalesce(value, ''). Чтобы считать пустую строку отсутствующим значением, используйте nullif(value, ''). Это условные выражения SQL, которые часто применяют вместе со строковыми функциями.