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

PostgreSQL содержит большой набор строковых функций для обработки текстовых данных: подсчета длины строки, объединения значений, удаления пробелов, изменения регистра, поиска подстрок, замены текста, работы с регулярными выражениями и форматирования строк.

В этом справочнике собраны основные функции PostgreSQL для работы со строками: ASCII, BIT_LENGTH, CHAR_LENGTH, CONCAT, CONCAT_WS, LEFT, RIGHT, LENGTH, LOWER, UPPER, LPAD, RPAD, LTRIM, RTRIM, TRIM, POSITION, REPLACE, REGEXP, REPEAT, REVERSE, SUBSTRING и другие операции, которые помогают очищать, преобразовывать и анализировать текстовые поля в SQL-запросах.

Что внутри:

функции для длины строки: BIT_LENGTH, CHAR_LENGTH, LENGTH;
объединение строк: CONCAT и CONCAT_WS;
извлечение части строки: LEFT, RIGHT, MID, SUBSTRING, SUBSTR;
изменение регистра: LOWER, UPPER, LCASE, UCASE;
удаление пробелов: LTRIM, RTRIM, TRIM;
дополнение строк: LPAD и RPAD;
поиск позиции и работа с подстроками: POSITION;
замена текста: REPLACE;
регулярные выражения: REGEXP;
повторение и разворот строки: REPEAT, REVERSE;
экранирование и кавычки: QUOTE.

Функции для работы со строками используется для проведения различных манипуляций со строками:

S. No.	Название и Описание
1	ASCII() Возвращает числовое значение самого левого символа
2	BIT_LENGTH() Возвращает длину аргумента в битах
3	CHAR_LENGTH() Возвращает количество символов в аргументе
4	CHARACTER_LENGTH() Синоним для CHAR_LENGTH()
5	CONCAT_WS() Возвращает конкатенацию с разделителем
6	CONCAT() Возвращает конкатенацию
7	LCASE() Синоним для LOWER()
8	LEFT() Возвращает крайнее левое количество символов, как указано в описании функции
9	LENGTH() Возвращает длину строки в байтах
10	LOWER() Возвращает аргумент в нижнем регистре
11	LPAD() Позволяет дополнить строку символами до нужного размера, добавляя заданные символы в начало строки.
12	LTRIM() Используется для удаления начальных пробелов из символьного выражения
13	MID() Возвращает подстроку, начиная с указанной позиции
14	POSITION() Синоним LOCATE()
15	QUOTE() Заключает строку в кавычки (если требуется) и экранирует специальные символы внутри строки подходящим для драйвера способом.
16	REGEXP Сопоставление с использованием регулярных выражений
17	REPEAT() Повторяет строку указанное количество раз
18	REPLACE() Заменяет последовательность символов в строке другим набором символов
19	REVERSE() Переворачивает символы в строке
20	RIGHT() Возвращает указанное крайнее правое количество символов
21	RPAD() Добавляет строку указанное количество раз
22	RTRIM() Удаляет конечные пробелы
24	SUBSTRING(), SUBSTR() Возвращает подстроку, как указано
25	TRIM() Удаляет начальные и конечные пробелы
26	UCASE() Синоним для UPPER()
27	UPPER() Преобразует в верхний регистр

ASCII(str)

Возвращает числовое значение крайнего левого символа строки. Возвращает 0, если строка является пустой строкой. Возвращает NULL, если строка имеет значение NULL. ASCII() работает для символов с числовыми значениями от 0 до 255:

testdb=# SELECT ASCII('2');
+---------------------------------------------------------+
| ASCII('2')                                              |
+---------------------------------------------------------+
| 50                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

testdb=# SELECT ASCII('dx');
+---------------------------------------------------------+
| ASCII('dx')                                             |
+---------------------------------------------------------+
| 100                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

BIT_LENGTH(str)

Возвращает длину строки в битах:

testdb=# SELECT BIT_LENGTH('text');
+---------------------------------------------------------+
| BIT_LENGTH('text')                                      |
+---------------------------------------------------------+
| 32                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CHAR_LENGTH(str)

Возвращает длину строки, измеренную в символах. Многобайтовый символ считается одним символом. Это означает, что для строки, содержащей пять двухбайтовых символов, LENGTH() возвращает 10, тогда как CHAR_LENGTH() возвращает 5:

testdb=# SELECT CHAR_LENGTH('text');
+---------------------------------------------------------+
| CHAR_LENGTH('text')                                     |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CHARACTER_LENGTH(str)

Синоним для CHAR_LENGTH().

CONCAT(str1,str2,...)

Возвращает строку, полученную в результате объединения аргументов. Может иметь один или несколько аргументов. Если все аргументы являются недвоичными строками, результатом будет недвоичная строка. Если аргументы включают какие-либо двоичные строки, результатом будет двоичная строка. Числовой аргумент преобразуется в эквивалентную ему форму двоичной строки; если Вы хотите избежать этого, воспользуйтесь примером ниже:

testdb=# SELECT CONCAT('My', 'S', 'QL');
+---------------------------------------------------------+
| CONCAT('My', 'S', 'QL')                                 |
+---------------------------------------------------------+
| MySQL                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() – сокращенная форма от Concatenate With Separator. Первый аргумент является разделителем для остальных аргументов. Разделитель добавляется между строками, которые необходимо объединить. Разделителем может быть строка, как и остальные аргументы. Если разделитель равен NULL, результат равен NULL:

testdb=# SELECT CONCAT_WS(',','First name','Last Name' );
+---------------------------------------------------------+
| CONCAT_WS(',','First name','Last Name' )                |
+---------------------------------------------------------+
| First name, Last Name                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LCASE(str)

Синоним для LOWER().

LEFT(str,len)

Возвращает крайние левые символы len из строки str или NULL, если какой-либо аргумент равен NULL:

testdb=# SELECT LEFT('foobarbar', 5);
+---------------------------------------------------------+
| LEFT('foobarbar', 5)                                    |
+---------------------------------------------------------+
| fooba                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LENGTH(str)

Возвращает длину строки str в байтах. Многобайтовый символ считается несколькими байтами. Это означает, что для строки, содержащей пять двухбайтовых символов, LENGTH() возвращает 10, тогда как CHAR_LENGTH() возвращает 5:

testdb=# SELECT LENGTH('text');
+---------------------------------------------------------+
| LENGTH('text')                                          |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOWER(str)

Возвращает строку со всеми символами, преобразованными в нижний регистр в соответствии с текущим сопоставлением набора символов:

testdb=# SELECT LOWER('QUADRATICALLY');
+---------------------------------------------------------+
| LOWER('QUADRATICALLY')                                  |
+---------------------------------------------------------+
| quadratically                                           |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LPAD(str,len,padstr)

Возвращает строку str, дополненную слева строкой padstr до длины len символов. Если str длиннее len, возвращаемое значение сокращается до len символов:

testdb=# SELECT LPAD('hi',4,'??');
+---------------------------------------------------------+
| LPAD('hi',4,'??')                                       |
+---------------------------------------------------------+
| ??hi                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LTRIM(str)

Возвращает строку str с удаленными начальными пробелами:

testdb=# SELECT LTRIM('  barbar');
+---------------------------------------------------------+
| LTRIM('  barbar')                                       |
+---------------------------------------------------------+
| barbar                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MID(str,pos,len)

MID(str,pos,len) – синоним для SUBSTRING(str,pos,len).

POSITION(substr IN str)

POSITION(substr IN str) – синоним для LOCATE(substr,str).

QUOTE_IDENT(string text), QUOTE_LITERAL(string text), QUOTE_LITERAL(value anyelement), QUOTE_NULLABLE(value anyelement)

Все эти функции возвращают заданную строку в кавычках для использования в качестве идентификатора в строке оператора SQL. В функции QUOTE_IDENT цитаты добавляются только при необходимости. В функции QUOTE_LITERAL встроенные одинарные кавычки и обратные косые черты удваиваются. Функция QUOTE_NULLABLE приводит заданное значение к тексту, а затем заключает его в кавычки как литерал; или, если аргумент NULL, функция также возвращает NULL.

Ниже приведены примеры для всех этих функций:

testdb=# SELECT QUOTE_IDENT('Foo bar');
 quote_ident
-------------
 "Foo bar"
(1 row)

testdb=# SELECT QUOTE_LITERAL(E'O\'Reilly');
 quote_literal
---------------
 'O''Reilly'
(1 row)

testdb=# SELECT QUOTE_LITERAL(42.5);
 quote_literal
---------------
 '42.5'
(1 row)

testdb=# SELECT QUOTE_NULLABLE(42.5);
 quote_nullable
----------------
 '42.5'
(1 row)

expr REGEXP pattern

Функция REGEXP_MATCHES(текст строки, текст шаблона, [ текст флагов]) выполняет сопоставление выражения expr с шаблоном. Возвращает 1, если expr соответствует pat; в противном случае возвращается 0. Если expr или pat равны NULL, результат равен NULL. REGEXP_MATCHES не чувствителен к регистру, за исключением случаев, когда он используется с двоичными строками.

Функция REGEXP_REPLACE(текст строки, текст шаблона, текст замены [, текст флагов]) заменяет подстроки, соответствующие регулярному выражению POSIX.

REGEXP_SPLIT_TO_ARRAY(текст строки, текст шаблона [, текст флагов]), Разделить строку, используя регулярное выражение POSIX в качестве разделителя.

REGEXP_SPLIT_TO_TABLE(текст строки, текст шаблона [, текст флагов]), разбивает строку, используя регулярное выражение POSIX в качестве разделителя.

Ниже приведены примеры для всех этих функций:

testdb=# SELECT REGEXP_MATCHES('ABCDEF','A%C%%');
 regexp_matches
----------------
(0 rows)

 
testdb=# SELECT REGEXP_REPLACE('Thomas', '.[mN]a.', 'M');
 regexp_replace
----------------
 ThM
(1 row)
 
 
testdb=# SELECT REGEXP_SPLIT_TO_ARRAY('hello world', E'\\s+');
 regexp_split_to_array
-----------------------
 {hello,world}
(1 row)
 
 
testdb=# SELECT REGEXP_SPLIT_TO_TABLE('hello world', E'\\s+');
 regexp_split_to_table
-----------------------
 hello
 world
(2 rows)

REPEAT(str,count)

Возвращает строку, состоящую из строки str, повторенной count количество раз. Если count меньше 1, возвращает пустую строку. Возвращает NULL, если str или count имеют значение NULL:

testdb=# SELECT REPEAT('SQL', 3);
   repeat
-----------
 SQLSQLSQL
(1 row)

REPLACE(str,from_str,to_str)

Возвращает строку str, в которой все вхождения строки from_str заменены строкой to_str. REPLACE() выполняет поиск с учетом регистра при поиске from_str:

testdb=# SELECT REPLACE('www.mysql.com', 'w', 'Ww');
      replace
------------------
 WwWwWw.mysql.com
(1 row)

REVERSE(str)

Возвращает строку str с обратным порядком символов:

testdb=# SELECT REVERSE('abcd');
 reverse
---------
 dcba
(1 row)

RIGHT(str,len)

Возвращает крайние правые символы len из строки str или NULL, если какой-либо аргумент равен NULL:

testdb=# SELECT RIGHT('foobarbar', 4);
 right
-------
 rbar
(1 row)

RPAD(str,len,padstr)

Возвращает строку str, дополненную справа строкой padstr до длины len символов. Если str длиннее len, возвращаемое значение сокращается до len символов:

testdb=# SELECT RPAD('hi',5,'?');
 rpad
-------
 hi???
(1 row)

RTRIM(str)

Возвращает строку str с удаленными пробелами в конце:

testdb=# SELECT RTRIM('barbar   ');
 rtrim
--------
 barbar
(1 row)

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

Формы без аргумента len возвращают подстроку из строки str, начиная с позиции pos. Формы с аргументом len возвращают подстроку длиной len символов из строки str, начиная с позиции pos. Формы, использующие FROM, являются стандартным синтаксисом SQL. Также можно использовать отрицательное значение для pos. В этом случае начало подстроки — это pos-символы с конца строки, а не с начала. Отрицательное значение может использоваться для pos в любой из форм этой функции:

testdb=# SELECT SUBSTRING('Quadratically',5);
substring
-----------
ratically
(1 row)

testdb=# SELECT SUBSTRING('foobarbar' FROM 4);
substring
-----------
barbar
(1 row)

testdb=# SELECT SUBSTRING('Quadratically',5,6);
substring
-----------
ratica
(1 row)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

Возвращает строку str со всеми удаленными префиксами или суффиксами remstr. Если не указан ни один из спецификаторов BOTH, LEADING или TRAILING, предполагается BOTH. remstr является необязательным, и, если он не указан, пробелы удаляются:

testdb=# SELECT TRIM('  bar   ');
 btrim
-------
 bar
 
testdb=# SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
 ltrim
--------
 barxxx

testdb=# SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
 btrim
-------
 bar

testdb=# SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
 rtrim
-------
 bar

UCASE(str)

Синоним для UPPER().

UPPER(str)

Возвращает строку str, в которой все символы заменены на прописные в соответствии с текущим сопоставлением набора символов:

testdb=# SELECT UPPER('manisha');
  upper
---------
 MANISHA
(1 row)

Вопрос: Какие строковые функции есть в PostgreSQL?

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

Вопрос: Чем LENGTH отличается от CHAR_LENGTH?

Ответ: LENGTH возвращает длину строки в байтах, а CHAR_LENGTH — количество символов. Для строк с кириллицей и другими многобайтовыми символами эти значения могут отличаться.

Вопрос: Как объединить строки в PostgreSQL?

Ответ: Для объединения строк используют CONCAT, CONCAT_WS или оператор ||. CONCAT_WS удобен, когда нужно объединить значения с разделителем.

Вопрос: Как удалить пробелы в строке?

Ответ: Для удаления пробелов используют TRIM, LTRIM и RTRIM. TRIM удаляет пробелы с обеих сторон, LTRIM — слева, RTRIM — справа.

Вопрос: Как заменить часть текста в PostgreSQL?

Ответ: Для замены фрагмента строки используют REPLACE. Если нужна более сложная логика поиска, можно применять регулярные выражения.

← Предыдущая статья

Числовые (математические) функции PostgreSQL