Самоучитель по SQL-сервер в Linux



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

В 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)