Строковые функции
В PostgreSQL существует множество разнообразных строковых функций, предназначенных для форматирования, анализа и сравнения строк. К их числу относятся как функции стандарта SQL92, так и нестандартные расширения PostgreSQL (например, ItrimO, rtrimO и substrO). Строковые функции PostgreSQL перечислены в табл. 5.10. В общем случае все, что говорится о типе text, в равной степени относится к значениям типа character и varchar.
Таблица 5.10. Строковые функции
| Функция | Описание |
| ascii(s) | Возвращает ASCII-код символа, переданного в виде строковой переменной s |
| btrim(s [, t]) | Возвращает строку s, в начале и в конце которой удалены все символы, входящие в строку t (если аргумент t не задан, усекаются начальные и конечные пропуски — пробелы, символы табуляции и т. д.) |
| char length(s) | Возвращает длину строки s в символах |
| chr(n) | Возвращает символ с ASCII-кодом n |
| s ilike(f) | Возвращает true, если выражение f совпадает (без учета регистра символов) с s |
| imtcap(s) | Возвращает строку s, в которой первая буква каждого слова преобразуется к верхнему регистру |
| length(s) | Возвращает длину строки s в символах |
| s like(f) | Возвращает true, если выражение f совпадает с s |
| lower (s) | Возвращает строку s, преобразованную к нижнему регистру |
| lpad(s. n [. c]) | Возвращает строку s, дополненную слева содержимым строки с (или пробелами, если аргумент с не задан) до длины n (или усеченную справа до n символов) |
| ltrim(s [. f]) | Возвращает строку s, в начале которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются начальные пропуски — пробелы, символы табуляции и т. д.) |
| octet_length(s) | Возвращает длину строки s в байтах |
| pos1tion(b IN s) | Возвращает позицию подстроки b в строке s (отсчет начинается с 1) |
| repeat (s, n) | Возвращает строку s, повторенную n раз |
| rpad(s. n. [. c]) | Возвращает строку s, дополненную справа содержимым строки с (или пробелами, если аргумент с не задан) до длины n (или усеченную слева до n символов) |
| rtrim(s [. f]) | Возвращает строку s, в конце которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются конечные пропуски — пробелы, символы табуляции и т. д.) |
| strpos(s. b) | Возвращает позицию подстроки b в строке s (отсчет начинается с 1). Относится к числу функций PostgreSQL и дублирует функцию SQL positionO, но с передачей аргументов в стиле С |
| substr(s. b [. 1]) | Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент 1 определяет максимальную длину подстроки в символах |
| substring FROM n FOR 1 ) |
Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент 1 определяет максимальную длину подстроки в символах |
| to_ascii (s. f) | Возвращает строку s, преобразованную из расширенной кодировки f в ASCII |
| translate(s. f. r) | Возвращает строку s, в которой все символы, входящие в строку f, заменяются соответствующими символами строки г |
| trim(направление f FROM s) | Возвращает строку s, в начале и/или в конце которой удалены все символы, входящие в строку f. В аргументе направление передается ключевое слово SQL, определяющее направление усечения (LEADING, TRAILING или BOTH) |
| upper(s) | Возвращает строку s, преобразованную к верхнему регистру |
Ниже приведены более подробные описания всех функций, перечисленных в таблице, с указанием аргументов, типов данных и выполняемых действий.
ascii()
Синтаксис: ascii(s)
Функция asci i () получает один аргумент — отдельный символ или строку типа text, и возвращает числовой ASCII-код первого интерпретированного символа. Результат возвращается в виде значения типа integer.
Пример
booktown=# SELECT asciitТ); ascii
84
(1 row)
booktown=# SELECT DISTINCT ON (substr)
booktown-# title. substrCtitle, 1, 1),
booktown-# ascii(tltle)
booktown-# FROM books
booktOMl-f ORDER BY substr ASC:
title | substr ascii
2001: A Space Odyssey | 2 | 50
Bartholomew and the Oobleck j В I 66
Dune |D 68
Franklin in the Dark | 70
Goodnignt Moon | G j 71
Little Women L 76
Practical PostgreSQL | P j 80
The Shining Т 84
(8 rows)
btrim()
Синтаксис:
btrim(s) btrim(s. t)
Функция получает один или два аргумента типа text, второй аргумент t не является обязательным. Если аргумент t задан, функция удаляет в начале и в конце строки s все символы, входящие в строку t. Если функция вызвана с одним аргументом, удаляются начальные и конечные пропуски — пробелы, символы табуляции и т. д.
Порядок перечисления символов в строке t для функции btrim() значения не имеет. В начале и конце строки s удаляются подстроки, полностью состоящие из символов, входящих в строку t.
Пример
booktown=# SELECT btrimC whitespace example ‘) AS trim_blanks,
booktown-# btr1m(‘123example 332’, ‘123’) AS trimjnumbers:
trim_blanks | trim_numbers
whitespace example example
(1 row)
char_length()
Синтаксис:
charjength(s)
Функция SQL92 char_l engthC) получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в символах. Возвращаемое значение относится к типу integer.
Пример
booktown=# SELECT charjength(title). title
booktown-l FROM books Dooktown-# LIMIT 3: charjength | title
11 | The Shining
4 Dune
21 I 2001: A Space Odyssey
(3 rows)
chr()
Синтаксис: chr(n)
Функция chr() получает один числовой аргумент п типа integer и возвращает символ с ASCII-кодом, равным п. Возвращаемое значение относится к типу text. Функция chr() фактически является обратной по отношению к функции asci 1 ().
Примеры
booktowrHf SELECT chr(65), ascii(‘A’);
chr ! ascii
A | 65
(1 row)
initcap()
Синтаксис: initcap(s)
Функция initcapO получает один аргумент s типа text, преобразует первые буквы всех слов к верхнему регистру и возвращает полученную строку. В данном контексте «словом» считается любая последовательность символов, отделенная от других слов пробелами.
Пример
booktown=# SELECT initcapCa prospective book title’):
initcap
A Prospective Book Title
(1 row)
length()
Синтаксис:
length(s)
Аналог функции SQL92 charj ength(). Получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в символах в виде значения типа integer.
Пример
booktown=# SELECT length(title), title
booktown-# FROM books
booktown-# LIMIT 3; length title
11 | The Shining 4 I Dune
21 | 2001: A Space Odyssey
(3 rows)
ПРИМЕЧАНИЕ
В стандарт SQL92 включены две функции определения длины строки: char_length() и octet_length(). Следовательно, эти две функции с большей, чем функция length(), вероятностью будут поддерживаться другими реляционными СУБД.
like() и ilike()
Синтаксис:
s like (f) s LIKE f like(s.f) s ilike(f) s HIKE f
Функция 1 i ke() проверяет, совпадает ли выражение, заданное аргументом f, со строкой s. При вызове функции либо передаются два аргумента типа text, либо используется специальный синтаксис SQL, в котором аргумент s предшествует имени функции. Функция 11 i ke() является нестандартной версией функции 11 ke(), игнорирующей регистр символов при сравнении, и вызывается только в синтаксисе SQL.
ПРИМЕЧАНИЕ
Ключевое слово SQL LIKE в PostgreSQL заменяется вызовом функции likeO. Возможность использования ключевого слова LIKE без круглых скобок — всего лишь синтаксическое удобство, на практике оба варианта эквивалентны.
Использование функции 1 ike() отличается от обычного сравнения с помощью оператора =, поскольку строка f может содержать символы подчеркивания (_) или процента (%), интерпретируемые особым образом. PostgreSQL интерпретирует символ _ как один произвольный символ, а символ % — как ноль или более произвольных символов. Эти специальные символы могут находиться в любом месте строки f.
В PostgreSQL существуют и более совершенные средства поиска по шаблону, о которых говорилось в пункте «Операторы регулярных выражений» подраздела «Строковые операторы» раздела «Операторы» этой главы.
Примеры
booktown=# SELECT * FROM books
booktown-# WHERE title LIKE (‘XRabbit1):
id i title | authorjd subjectjd
1234 | The Velveteen Rabbit | 25041 | 3
(1 row)
booktown=# SELECT * FROM books
booktown-# WHERE title LIKE
id | title | authorjd | subjectjd
4513 | Dune | 1866 | 15
25908 | Franklin in the Dark | 15990 | 2 (2 rows)
booktown=# SELECT * FROM books
booktown-# WHERE title ILIKE ‘«python*’:
id | title | authorjd | subjectjd
41473 | Programming Python | 7805 | 4 41477
Learning Python | 7805 4
(2 rows)
lower()
Синтаксис: lower(s)
Функция SQL92 1 ower () получает один аргумент типа text, преобразует все символы строки к нижнему регистру и возвращает полученную строку в виде значения типа text.
Пример
booktown=# SELECT lower(title)
booktown-# FROM books
booktown-# LIMIT 3;
lower
tne shining
dune
2001: a space odyssey
lpad()
Синтаксис:
lpad(s. n) 1pacl(s. n. c)
Функция lpad() вызывается с двумя или тремя аргументами s, n и с (необязательный аргумент), относящимися к типам text, Integer и text соответственно. Строка s дополняется слева до длины n символов пробелами или содержимым необязательного аргумента с.
Если начальная длина строки s больше п, строка усекается справа до длины п.
Пример
booktown=# SELECT title. IpadCtitle, 12, AS dashed,
booktown-# IpadCtitle. 12. ‘-+-‘) AS pius_dashed
booktown-# FROM books LIMIT 4;
title | dashed | plus_dashed _
The Shining | -The Shining | -The Shining
Dune |Dune Dune
2001: A Space Odyssey | 2001: A Spac | 2001: A Spac
The Cat in the Hat | The Cat in t | The Cat in t
(4 rows)
Itrim()
Синтаксис:
Itrim(s) ItrimCs. f)
Функция 11 ri m() получает один или два аргумента типа text (аргумент f не обязателен). Если аргумент f не задан, функция возвращает строку s, из которой удалены все начальные пробелы. В противном случае удаляется начальная подстрока, состоящая только из символов, содержащихся в f. Если такая подстрока не найдена, s остается без изменений.
Примеры
booktown=# SELECT ItrimC whitespace example’):
Itrim
whitespace example (1 row)
booktown=# SELECT title. ItrimCtitle, ‘TD2he ‘)
booktown-# FROM books booktown-# LIMIT 4:
title | Itritn
The Shining | Shining
Dune I une
2001: A Space Odyssey | 001: A Space Odyssey
The Cat in the Hat j Cat In the Hat
(4 rows)
octet_length()
Синтаксис: octetjength(s)
Функция SQL92 octetj ength() получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в байтах. Возвращаемое значение относится к типу integer.
В большинстве случаев длина строки в байтах совпадает с количеством символов, хотя в расширенных кодировках не всегда, поскольку в них символ по определению может состоять из нескольких байтов.
Пример
booktown=# SELECT title, octetjength(title)
booktown-# FROM books
booktown-# ORDER BY title ASC
booktown-# LIMIT 3:
title octet Jength
2001: A Space Odyssey | 21
Bartholomew and the Oobleck | 27
Dune | 4
(3 rows)
position()
Синтаксис: position(b IN s)
Функция SQL92 position^) получает два аргумента, относящихся к типу text, и возвращает начальную позицию подстроки b в строке s (отсчет начинается с 1). Возвращаемое значение относится к типу i nteger. Если подстрока не найдена, функция возвращает 0.
Пример
booktown=# SELECT title, positiorK’the’ IN title) AS the_pos
booktown-# FROM books
booktown-# WHERE positionCthe1 IN title) != 0:
title | the_pos
The Cat in the Hat | 12
Bartholomew and the Oobleck | 17
Franklin in the Dark 13
(3 rows)
repeat()
Синтаксис: repeat(s. n)
Функция repeat О получает два аргумента s и п, относящихся к типам text и integer соответственно. Функция возвращает символьную строку s, повторенную п раз, в виде значения типа text.
Пример
booktown=# SELECT repeat(lastjname. 2)
booktown-l FROM authors booktown-# LIMIT 3; repeat
DenhamDenham BourgeoisBourgeois BiancoBianco
(3 rows)
rpad()
Синтаксис:
rpad(s. n) rpad(s. n. c)
Функция rpadO похожа на функцию IpadO, но дополняет строку справа, а не слева. Она вызывается с двумя или тремя аргументами s, n и с (необязательный аргумент), относящимися к типам text, integer и text соответственно. Строка s дополняется справа до длины п символов пробелами или содержимым необязательного аргумента с.
Если начальная длина строки s больше п, строка усекается слева до длины п.
Пример
booktown=# SELECT rpad(‘whitespace example’. 30): rpad
whitespace example (1 row)
booktown=# SELECT title. rpacKtitle. 12, ‘ -‘) AS right_dashed.
booktown-# rpad(title, 12, ‘-+-‘) AS right_plus_dashed
booktown-# FROM books
booktown-f LIMIT 3:
title [ right_dashed | right_plus_dashed
The Shining The Shining- | The Shining-Dune Dune
2001: A Space Odyssey 2001: A Spac 2001: A Spac
(3 rows)
rtrim()
Синтаксис:
rtrim(s)
trlm(s. f)
Функция rtri m() получает один или два аргумента типа text (аргумент f не обязателен). Если аргумент f не задан, функция возвращает строку s, из которой удалены все завершаю nine пробелы. В противном случае удаляется завершающая подстрока, состоящая только из символов, содержащихся в f. Если такая подстрока не найдена, s остается без изменений.
Примеры
booktown=# SELECT rtrimC’whitespace example ‘);
rtrim
whitespace example
(1 row)
booktown=# SELECT title, rtrinKtitle, ‘yes’)
booktown-# FROM books
booktown-# LIMIT 4;
title rtrim
The Shining | The Shining
Dune Dun 2001: A Space Odyssey 2001: A Space Od
The Cat in the Hat j The Cat in the Hat
(4 rows)
strpos()
Синтаксис: strpos(s. b)
Функция strposO эквивалентна функции SQL92 positionO, но аргументы передаются ей в стиле С. Функция получает два аргумента, относящихся к типу text, и возвращает начальную позицию подстроки b в строке s (отсчет начинается с 1). Возвращаемое значение относится к типу 1 nteger. Если подстрока не найдена, функция возвращает 0.
Пример
booktown=# SELECT title, strposdower(title), ‘rabbit’)
booktown-l FROM books
booktown-# WHERE strposdower(title), ‘rabbit’) != 0;
title | strpos
The Velveteen Rabbit | 15
(1 row)
substr()
Синтаксис:
substr(s. n) substr(s. n. 1)
Функция substrO эквивалентна функции SQL92 substring!), но аргументы передаются ей в стиле С. Функция вызывается с двумя или тремя аргументами s, n и 1 (необязательный аргумент), относящимися к типам text, т nteger и i nteger соответственно. Возвращаемое значение представляет собой подстроку s, начинающуюся с позиции п. Необязательный аргумент 1 определяет максимальную длину подстроки в символах.
Если заданная длина подстроки превышает количество оставшихся символов в строке s, возвращается только остаток строки. Иначе говоря, возвращаемое значение не дополняется до заданной длины.
Пример
booktown=# SELECT title, substr(title, 15). substr(title. 5. 9)
booktown-# FROM books
booktown-# ORDER BY title DESC
booktown-# LIMIT 3;
title | substr | substr
The Velveteen Rabbit Rabbit | Velveteen
The Tell-Tale Heart Heart Tell-Tale
The Shining | | Shining
(3 rows)
substring()
Синтаксис:
substrts. n) substr(s, n. 1)
Функция SQL92 substring() эквивалентна функции PostgreSQLsubstr(). Функция вызывается с двумя или тремя аргументами s, n и 1 (необязательный аргумент), относящимися к типам text, integer и integer соответственно. Возвращаемое значение представляет собой подстроку s, начинающуюся с позиции п. Необязательный аргумент 1 определяет максимальную длину подстроки в символах.
Примеры
booktown=# SELECT title, substring(title FROM 15)
booktown-# FROM books
booktown-* ORDER BY title DESC
booktown-# LIMIT 3;
title | substring
The Velveteen Rabbit | Rabbit
The Tell-Tale Heart Heart The Shining
(3 rows)
booktown=# SELECT title, substring(title FROM 5 FOR 9)
booktown-# FROM books
booktown-# ORDER BY title DESC booktown-# LIMIT 3;
title | substring
The Velveteen Rabbit Velveteen
The Tell-Tale Heart Tell-Tale
The Shining | Shining
(3 rows)
to_ascii()
Синтаксис:
to_ascii(s, f)
Функция to_asci i () получает строковый аргумент s, относящийся к типу text, и обозначение расширенной кодировки f, а возвращает обычный ASCII-текст в виде значения типа text.
Допустимы следующие обозначения расширенных кодировок: LATIN1 (ISO 8859-1), LATIN2 (ISO 8859-2) и WIN1250 (Windows CP1250 или WinLatin2). Для работы функции необходима поддержка расширенных кодировок (устанавливается при помощи ключа командной строки компилятора при установке PostgreSQL).
Пример
booktown=# SELECT to_ascii(‘Multibyte Source’, ‘LATIN1’);
to_ascii
Multibyte Source
(1 row)
translate()
Синтаксис: translate(s. f. r)
Функция transl ate() получает три аргумента s, f и г, относящихся к типу text. В строке s все символы, входящие в строку f, заменяются символами с тем же индексом из строки г. Возвращаемое значение относится к типу text.
Обратите внимание: функция заменяет не экземпляры всей строки f, а любые символы, входящие в эту строку. Если f содержит больше символов, чем г, то все символы f, не имеющие аналогов в г, просто удаляются из s (удобный способ удаления ненужных символов).
В следующем примере все вопросительные знаки заменяются восклицательными.
Пример
booktown=# SELECT translated am an example?’, ‘?’, ‘!’):
translate
I am an example!
(1 row)
В следующем примере все вхождения символа «i» заменяются символом «w», а все вхождения символа «s» — символом «а». Лишний символ в конце строки «was» игнорируется.
Пример
booktown=# SELECT translate(‘This is a mistake.’, ‘is’, ‘was’);
translate
Thwa wa a mwatake.
(1 row)
В последнем примере все гласные буквы заменяются пустой строкой, то есть удаляются из входной строки.
Пример
booktown=# SELECT title.
booktown-# translateCtitle, ‘aeiouAEIOU’, ») AS vowelless
booktown-# FROM books
booktown-f LIMIT 5;
title | vowelless
The Shining Th Shnng
Dune I Dn
2001: A Space Odyssey | 2001: Spc dyssy
The Cat In the Hat | Th Ct n th Ht
Bartholomew and the Oobleck Brthlmw nd th blck
(5 rows)
trim()
Синтаксис:
trim(направление f FROM s)
Функция SQL92 trim() способна заменить функции PostgreSQL rtrim(), ltrim() и btrim(). При вызове она получает три аргумента: ключевое слово (LEADING, TRAILING или BOTH) и две строки, f и s.
С ключевым словом LEADING функция trim() работает аналогично функции ItrimO, удаляя в начале строки s наибольшую подстроку, все символы которой входят в f.
С ключевым словом TRAILING функция trim() работает аналогично функции rtrim(), удаляя в конце строки s наибольшую подстроку, все символы которой входят в f.
С ключевым словом BOTH функция trim() работает аналогично функции btrim(), удаляя в начале и в конце строки s наибольшие подстроки, все символы которых входят в f.
Примеры
booktowrH1 SELECT isbn, trim(LEADING ‘0’ FROM isbn)
booktown-# FROM editions booktown-# LIMIT 2; isbn | Itrim
039480001X 39480001X 0451160916 451160916
(2 rows)
booktown=# SELECT isbn, trimCTRAILING ‘X’ FROM isbn)
booktown-# FROM editions booktown-# LIMIT 2;
1Sbn rtrim
039480001X j 039480001 0451160916 j 0451160916
(2 rows)
booktowrHf SELECT isbn, trimCBOTH ‘OX’ FROM isbn)
booktown-# FROM editions booktown-# LIMIT 2; isbn I btrim
039480001X | 39480001 0451160916 | 451160916
(2 rows)
upper()
Синтаксис:
upper(s)
Функция SQL92 upper() получает один аргумент типа text, преобразует все символы строки к верхнему регистру и возвращает полученную строку в виде значения типа text.
Пример
booktown=# SELECT title, upper(title)
booktown-# FROM books
booktown-# ORDER BY id ASC
booktown-# LIMIT 3;
title | upper
The Tell-Tale Heart | THE TELL-TALE HEART
Little Women j LITTLE WOMEN
The Velveteen Rabbit | THE VELVETEEN RABBIT
(3 rows)