SQL — Подстановочные знаки
Символ подстановки используется для замены любого другого символа в строке. Подстановочные символы используются с оператором SQL LIKE . Оператор LIKE используется в предложении WHERE для поиска заданного шаблона в столбце. В сочетании с оператором LIKE используются две подстановочные знаки:
- % — Знак процента представляет нулевой, один или несколько символов
- _ — Подчеркнутый символ представляет собой один символ
Примеры, с разными LIKE-операторами с «%» и «_» подстановочными знаками:
| Выражение | Описание |
| WHERE name LIKE 'text%' | Находит любые значения, начинающиеся с "text" |
| WHERE name LIKE '%text' | Находит любые значения, заканчивающиеся на "text" |
| WHERE name LIKE '%text%' | Находит любые значения, которые имеют «text» в любой позиции |
| WHERE name LIKE '_text%' | Находит любые значения, которые имеют «text» во второй позиции |
| WHERE name LIKE 'text_%_%' | Находит любые значения, начинающиеся с «text» и длиной не менее 3 символов |
| WHERE name LIKE 'text%data' | Находит любые значения, начинающиеся с «text» и заканчивающиеся на «data» |
Использование символа %
Следующий оператор SQL выбирает всех пользователей с name, начинающимся с «Т»:
Пример:
Следующий оператор SQL выбирает всех пользователей с именем, содержащим шаблон «То»:
Пример:
Использование подстановочного знака
Следующий оператор SQL выбирает всех пользователей с name, начиная с любого символа, за которым следует «о»:
Пример:
Следующий оператор SQL выбирает всех пользователе с name начиная с «Т», за которым следует любой символ, за которым следует «м», за которым следует любой символ, а затем «с»:
Пример:
Использование подстановочного знака [charlist]
Следующий оператор SQL выбирает всех пользователей с name, начиная с «Т», «Р» или «Е»:
Пример:
Следующий оператор SQL выбирает всех пользователей с name, начиная с «Т», «Р» или «Е»:
Пример:
Использование подстановочного знака [! Charlist]
Два следующих оператора SQL выбирают всех пользователей с помощью name NOT, начинающегося с «Т», «Р» или «E»:
Функции SUBSTR и INSTR в Oracle SQL
В посте рассматриваются однострочные функции SUBSTR и INSTR, работающие с символьными данными.
Символьные данные или строки являются универсальными, т.к. они позволяют хранить практически любой тип данных. Функции, которые работают с символьными данными, классифицируются на функции преобразования регистра символов и манипулирования символами.
Функции манипулирования символами используются для извлечения, преобразования и форматирования символьных строк. К этому классу относятся функции CONCAT, LENGTH, LPAD, RPAD, TRIM, REPLACE и рассматриваемые нижу функции SUBSTR и INSTR.
Функция SUBSTR принимает три параметра и возвращает строку, состоящую из количества символов, извлеченных из исходной строки, начиная с указанной начальной позиции:
SUBSTR (строка, начальная позиция, количество символов).
В приведенном примере извлекаются символы с первой по четвертую позиции из значений колонки last_name. Для сравнения выводятся исходные значения колонки last_name.
Функция INSTR возвращает число, представляющее позицию в исходной строке, начиная с заданной начальной позиции, где n-ное вхождение элемента поиска начинается:
INSTR (строка, элемент поиска, [начальная позиция], [n-ное вхождение элемента поиска]
Следующий запрос показывает позицию строчной буквы a для каждой строки колонки last_name. Если в строке встречаются два или более символов a, то будет отображена позиция первого/начального из них. Для сравнения и анализа выводятся исходные значения колонки.
Если необходимо также отобразить позицию заглавной буквы А в фамилии, то надо предварительно перевести все символы фамилии в строчные, используя вложенную функцию LOWER. Запрос выглядит следующим образом:
Как видно из результата, теперь позиция заглавной буквы A тоже определяется, например, для Abel, Ande, Atkinson, Austin возвращается значение 1.
В посте приведен пример совместного применения таких функций, как LENGTH, SUBSTR и INSTR.
Значения до какого то символа 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.