Какая из этих строковых функций sql допустима
В этом разделе описаны функции и операторы для работы с текстовыми строками. Под строками в данном контексте подразумеваются значения типов character , character varying и text . Если не отмечено обратное, все нижеперечисленные функции работают со всеми этими типами, хотя с типом character следует учитывать возможные эффекты автоматического дополнения строк пробелами. Некоторые из этих функций также поддерживают битовые строки.
Примечание
До версии 8.3 в PostgreSQL эти функции также прозрачно принимали значения некоторых не строковых типов, неявно приводя эти значения к типу text . Сейчас такие приведения исключены, так как они часто приводили к неожиданным результатам. Однако оператор конкатенации строк ( || ) по-прежнему принимает не только строковые данные, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9.8. Во всех остальных случаях для повторения предыдущего поведения потребуется добавить явное преобразование в text .
Таблица 9.8. Строковые функции и операторы языка SQL
| Функция | Тип результата | Описание | Пример | Результат |
|---|---|---|---|---|
| string || string | text | Конкатенация строк | ‘Post’ || ‘greSQL’ | PostgreSQL |
| string || не string или не string || string | text | Конкатенация строк с одним не строковым операндом | ‘Value: ‘ || 42 | Value: 42 |
| bit_length( string ) | int | Число бит в строке | bit_length(‘jose’) | 32 |
| char_length( string ) или character_length( string ) | int | Число символов в строке | char_length(‘jose’) | 4 |
| lower( string ) | text | Переводит символы строки в нижний регистр | lower(‘TOM’) | tom |
| octet_length( string ) | int | Число байт в строке | octet_length(‘jose’) | 4 |
| overlay( string placing string from int [ for int ]) | text | Заменяет подстроку | overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) | Thomas |
| position( substring in string ) | int | Положение указанной подстроки | position(‘om’ in ‘Thomas’) | 3 |
| substring( string [ from int ] [ for int ]) | text | Извлекает подстроку | substring(‘Thomas’ from 2 for 3) | hom |
| substring( string from шаблон ) | text | Извлекает подстроку, соответствующую регулярному выражению в стиле POSIX. Подробно шаблоны описаны в Разделе 9.7. | substring(‘Thomas’ from ‘. $’) | mas |
| substring( string from шаблон for спецсимвол ) | text | Извлекает подстроку, соответствующую регулярному выражению в стиле SQL . Подробно шаблоны описаны в Разделе 9.7. | substring(‘Thomas’ from ‘%#»o_a#»_’ for ‘#’) | oma |
| trim([ leading | trailing | both ] [ characters ] from string ) | text | Удаляет наибольшую подстроку, содержащую только символы characters (по умолчанию пробелы), с начала ( leading ), с конца ( trailing ) или с обеих сторон ( both , (по умолчанию)) строки string | trim(both ‘xyz’ from ‘yxTomxx’) | Tom |
| trim([ leading | trailing | both ] [ from ] string [ , characters ] ) | text | Нестандартный синтаксис trim() | trim(both from ‘yxTomxx’, ‘xyz’) | Tom |
| upper( string ) | text | Переводит символы строки в верхний регистр | upper(‘tom’) | TOM |
Кроме этого, в PostgreSQL есть и другие функции для работы со строками, перечисленные в Таблице 9.9. Некоторые из них используются в качестве внутренней реализации стандартных строковых функций SQL , приведённых в Таблице 9.8.
Таблица 9.9. Другие строковые функции
Функции concat , concat_ws и format принимают переменное число аргументов, так что им для объединения или форматирования можно передавать значения в виде массива, помеченного ключевым словом VARIADIC (см. Подраздел 38.5.5). Элементы такого массива обрабатываются, как если бы они были обычными аргументами функции. Если вместо массива в соответствующем аргументе передаётся NULL, функции concat и concat_ws возвращают NULL, а format воспринимает NULL как массив нулевого размера.
См. также агрегатную функцию string_agg в Разделе 9.20.
Таблица 9.10. Встроенные преобразования
[a] Имена преобразований следуют стандартной схеме именования. К официальному названию исходной кодировки, в котором все не алфавитно-цифровые символы заменяются подчёркиваниями, добавляется _to_ , а за ним аналогично подготовленное имя целевой кодировки. Таким образом, имена кодировок могут не совпадать буквально с общепринятыми названиями.
9.4.1. format
Функция format выдаёт текст, отформатированный в соответствии со строкой формата, подобно функции sprintf в C.
formatstr — строка, определяющая, как будет форматироваться результат. Обычный текст в строке формата непосредственно копируется в результат, за исключением спецификаторов формата. Спецификаторы формата представляют собой местозаполнители, определяющие, как должны форматироваться и выводиться в результате аргументы функции. Каждый аргумент formatarg преобразуется в текст по правилам выводам своего типа данных, а затем форматируется и вставляется в результирующую строку согласно спецификаторам формата.
Спецификаторы формата предваряются символом % и имеют форму
Строка вида n $ , где n — индекс выводимого аргумента. Индекс, равный 1, выбирает первый аргумент после formatstr . Если позиция опускается, по умолчанию используется следующий аргумент по порядку. флаги (необязателен)
Дополнительные параметры, управляющие форматированием данного спецификатора. В настоящее время поддерживается только знак минус ( — ), который выравнивает результата спецификатора по левому краю. Он работает, только если также определена ширина . ширина (необязателен)
Задаёт минимальное число символов, которое будет занимать результат данного спецификатора. Выводимое значение выравнивается по правой или левой стороне (в зависимости от флага — ) с дополнением необходимым числом пробелов. Если ширина слишком мала, она просто игнорируется, т. е. результат не усекается. Ширину можно обозначить положительным целым, звёздочкой ( * ), тогда ширина будет получена из следующего аргумента функции, или строкой вида * n $ , тогда ширина будет задаваться в n -ом аргументе функции.
Если ширина передаётся в аргументе функции, этот аргумент выбирается до аргумента, используемого для спецификатора. Если аргумент ширины отрицательный, результат выравнивается по левой стороне (как если бы был указан флаг — ) в рамках поля длины abs ( ширина ). тип (обязателен)
Тип спецификатора определяет преобразование соответствующего выводимого значения. Поддерживаются следующие типы:
s форматирует значение аргумента как простую строку. Значение NULL представляется пустой строкой.
I обрабатывает значение аргумента как SQL-идентификатор, при необходимости заключая его в кавычки. Значение NULL для такого преобразования считается ошибочным (так же, как и для quote_ident ).
В дополнение к спецификаторам, описанным выше, можно использовать спецпоследовательность %% , которая просто выведет символ % .
Несколько примеров простых преобразований формата:
Следующие примеры иллюстрируют использование поля ширина и флага — :
Эти примеры показывают применение полей позиция :
В отличие от стандартной функции C sprintf , функция format в PostgreSQL позволяет комбинировать в одной строке спецификаторы с полями позиция и без них. Спецификатор формата без поля позиция всегда использует следующий аргумент после последнего выбранного. Кроме того, функция format не требует, чтобы в строке формата использовались все аргументы функции. Пример этого поведения:
Спецификаторы формата %I и %L особенно полезны для безопасного составления динамических операторов SQL. См. Пример 43.1.
Строковые функции Sql
Обратите внимание, объединение произошло без разделения, что не очень читабельно. Давайте подправим наш запрос, чтобы между объединяемыми столбцами был пробел:
Как видите, пробел считается тоже аргументом и указывается через запятую. Если объединяемых столбцов было бы больше, то указывать каждый раз пробелы было бы нерационально. В этом случае можно было бы использовать строковую функцию CONCAT_WS(разделитель, str1,str2. ) , которая помещает разделитель между объединяемыми строками (разделитель указывается, как первый аргумент). Наш запрос тогда будет выглядеть так:
Встроенные функции
Для работы со строками в T-SQL можно применять следующие функции:
LEN : возвращает количество символов в строке. В качестве параметра в функцию передается строка, для которой надо найти длину:
LTRIM : удаляет начальные пробелы из строки. В качестве параметра принимает строку:
RTRIM : удаляет конечные пробелы из строки. В качестве параметра принимает строку:
CHARINDEX : возвращает индекс, по которому находится первое вхождение подстроки в строке. В качестве первого параметра передается подстрока, а в качестве второго — строка, в которой надо вести поиск:
PATINDEX : возвращает индекс, по которому находится первое вхождение определенного шаблона в строке:
LEFT : вырезает с начала строки определенное количество символов. Первый параметр функции — строка, а второй — количество символов, которые надо вырезать сначала строки:
RIGHT : вырезает с конца строки определенное количество символов. Первый параметр функции — строка, а второй — количество символов, которые надо вырезать сначала строки:
SUBSTRING : вырезает из строки подстроку определенной длиной, начиная с определенного индекса. Певый параметр функции — строка, второй — начальный индекс для вырезки, и третий параметр — количество вырезаемых символов:
REPLACE : заменяет одну подстроку другой в рамках строки. Первый параметр функции — строка, второй — подстрока, которую надо заменить, а третий — подстрока, на которую надо заменить:
REVERSE : переворачивает строку наоборот:
CONCAT : объединяет две строки в одну. В качестве параметра принимает от 2-х и более строк, которые надо соединить:
LOWER : переводит строку в нижний регистр:
UPPER : переводит строку в верхний регистр
SPACE : возвращает строку, которая содержит определенное количество пробелов
Строковые функции SQL
Функция CONV() преобразует числа из одной системы счисления в другие. Она имеет следующий синтаксис:
CONV(число,N,M)
Аргумент число находится в системе счисления с основанием N. Функция переводит его в систему счисления с основанием M и возвращает значение в виде строки.
Следующий запрос переводит число 2 из десятичной системы счисления в двоичную:
Для перевода числа 2E из шестнадцатиричной системы в десятичную требуется запрос:
Функция CHAR() переводит ASCII-код в строки. Она имеет следующий синтаксис:
CHAR(n1,n2,n3..)
Следующие функции возвращают длину строки:
- LENGTH(строка);
- OCTET_LENGTH(строка);
- CHAR_LENGTH(строка);
- CHARACTER_LENGTH(строка).
Иногда бывает полезной функция BIT_LENGTH(строка), которая возвращает длину строки в битах.
Функции работы с подстроками
Подстрокой обычно называют часть строки. Часто требуется узнать позицию первого вхождения подстроки в строку. Эту задачу в MySQL решают три функции:
- LOCATE(подстрока, строка [,позиция]);
- POSITION(подстрока, строка);
- INSTR(строка, подстрока).
Если подстрока не содержится в строке, то все три функции возвращают значение 0. Функция INSTR() отличается от двух других порядком аргументов. Функция LOCATE() может содержать третий аргумент позиция, который позволяет искать подстроку в строке не с начала, а с указанной позиции.
SELECT LOCATE(‘Топаз’, ‘открытое акционерное общество Топаз’);
SELECT POSITION(‘Топаз’, ‘открытое акционерное общество Топаз’);
SELECT INSTR(‘открытое акционерное общество Топаз’,’Топаз’);
SELECT LOCATE(‘Топаз’, ‘ Завод Топаз и ООО Топаз’, 9);
SELECT LOCATE(‘Алмаз’, ‘открытое акционерное общество Топаз’);
Функции LEFT(строка, N) и RIGHT(строка, N) возвращают соответственно крайние левые и крайние правые N символов в строке.
SELECT LEFT(‘СУБД MySQL’, 4);
SELECT RIGHT(‘СУБД MySQL’, 5);
Иногда требуется получить подстроку, которая начинается с некоторой заданной позиции. Для этого используются функции:
- SUBSTRING(строка, позиция, N);
- MID(строка, позиция, N).
Обе функции возвращают N символов заданной строки, расположенных начиная с указанной позиции.
SELECT SUBSTRING(‘СУБД MySQL — одна из самых популярных СУБД’, 6,5);
При работе с электронными адресами и адресами сайтов очень полезна функция SUBSTR_INDEX(). Функция имеет три аргумента:
SUBSTR_INDEX(строка, разделитель, N).
Аргумент N может быть положительным или отрицательным. Если он отрицательный, то функция находит N-ое вхождение разделителя, если считать справа. После чего возвращает подстроку, расположенную справа от найденного разделителя. Если N положительно, то функция находит N-ое вхождение разделителя слева и возвращает подстроку, расположенную слева от найденного разделителя.
Функция REPLACE(строка,подстрока1,подстрока2) позволяет заменить в строке все вхождения подстроки1 на подстроку2.
SELECT REPLACE(‘ООО Топаз’,’Топаз’,’АЛМАЗ’);
Результат: ООО АЛМАЗ
Функции удаления пробелов
Для удаления пробелов в строке используются следующие функции:
- LTRIM(строка) – удаляет пробелы в начале строки;
- RTRIM(строка) – удаляет пробелы в конце строки;
- TRIM(строка) – удаляет все пробелы.
SELECT TRIM(‘ www.mysql.ru ‘);
Функцию TRIM() можно использовать и с более сложным синтаксисом:
TRIM([ [BOTH | LEADING | TRAILING] [подстрока] FROM] строка)
В этом случае функция удаляет не пробелы, а все вхождения подстроки. При этом если задан аргумент LEADING, то только слева. Если задан аргумент TRAILING, то только справа. Если задан аргумент BOTH, то и слева и справа.