Найти число в строке oracle

Обновлено: 06.07.2024

Во всех функциях, поиск регистрозависимый по умолчанию. Существуют варианты функций для регистронезависимого поиска.

position(haystack, needle), locate(haystack, needle)

Поиск подстроки needle в строке haystack .

Возвращает позицию (в байтах) найденной подстроки в строке, начиная с 1, или 0, если подстрока не найдена.

Для поиска без учета регистра используйте функцию positionCaseInsensitive.

Синтаксис

Алиас: locate(haystack, needle[, start_pos]) .

Синтаксис position(needle IN haystack) обеспечивает совместимость с SQL, функция работает так же, как position(haystack, needle) .

Аргументы

  • haystack — строка, по которой выполняется поиск. Строка.
  • needle — подстрока, которую необходимо найти. Строка.
  • start_pos — опциональный параметр, позиция символа в строке, с которого начинается поиск. UInt.

Возвращаемые значения

  • Начальная позиция в байтах (начиная с 1), если подстрока найдена.
  • 0, если подстрока не найдена.

Примеры

Фраза «Hello, world!» содержит набор байт, представляющий текст в однобайтовой кодировке. Функция возвращает ожидаемый результат:

Аналогичная фраза на русском содержит символы, которые не могут быть представлены в однобайтовой кодировке. Функция возвращает неожиданный результат (используйте функцию positionUTF8 для символов, которые не могут быть представлены одним байтом):

Примеры работы функции с синтаксисом POSITION(needle IN haystack)

positionCaseInsensitive

Такая же, как и position, но работает без учета регистра. Возвращает позицию в байтах найденной подстроки в строке, начиная с 1.

Работает при допущении, что строка содержит набор байт, представляющий текст в однобайтовой кодировке. Если допущение не выполнено — то возвращает неопределенный результат (не кидает исключение). Если символ может быть представлен с помощью двух байтов, он будет представлен двумя байтами и так далее.

Синтаксис

Аргументы

  • haystack — строка, по которой выполняется поиск. Строка.
  • needle — подстрока, которую необходимо найти. Строка.
  • start_pos — опциональный параметр, позиция символа в строке, с которого начинается поиск. UInt.

Возвращаемые значения

  • Начальная позиция в байтах (начиная с 1), если подстрока найдена.
  • 0, если подстрока не найдена.

Пример

positionUTF8

Возвращает позицию (в кодовых точках Unicode) найденной подстроки в строке, начиная с 1.

Работает при допущении, что строка содержит набор кодовых точек, представляющий текст в кодировке UTF-8. Если допущение не выполнено — то возвращает неопределенный результат (не кидает исключение). Если символ может быть представлен с помощью двух кодовых точек, он будет представлен двумя и так далее.

Для поиска без учета регистра используйте функцию positionCaseInsensitiveUTF8.

Синтаксис

Аргументы

  • haystack — строка, по которой выполняется поиск. Строка.
  • needle — подстрока, которую необходимо найти. Строка.
  • start_pos — опциональный параметр, позиция символа в строке, с которого начинается поиск. UInt.

Возвращаемые значения

  • Начальная позиция в кодовых точках Unicode (начиная с 1), если подстрока найдена.
  • 0, если подстрока не найдена.

Примеры

Фраза «Привет, мир!» содержит набор символов, каждый из которых можно представить с помощью одной кодовой точки. Функция возвращает ожидаемый результат:

Запрос для символа é , который представлен одной кодовой точкой U+00E9 :

Запрос для символа é , который представлен двумя кодовыми точками U+0065U+0301 :

positionCaseInsensitiveUTF8

Такая же, как и positionUTF8, но работает без учета регистра. Возвращает позицию (в кодовых точках Unicode) найденной подстроки в строке, начиная с 1.

Работает при допущении, что строка содержит набор кодовых точек, представляющий текст в кодировке UTF-8. Если допущение не выполнено — то возвращает неопределенный результат (не кидает исключение). Если символ может быть представлен с помощью двух кодовых точек, он будет представлен двумя и так далее.

Синтаксис

Аргументы

  • haystack — строка, по которой выполняется поиск. Строка.
  • needle — подстрока, которую необходимо найти. Строка.
  • start_pos — опциональный параметр, позиция символа в строке, с которого начинается поиск. UInt.

Возвращаемые значения

  • Начальная позиция в байтах (начиная с 1), если подстрока найдена.
  • 0, если подстрока не найдена.

Пример

multiSearchAllPositions

The same as position but returns Array of positions (in bytes) of the found corresponding substrings in the string. Positions are indexed starting from 1.

The search is performed on sequences of bytes without respect to string encoding and collation.

  • For case-insensitive ASCII search, use the function multiSearchAllPositionsCaseInsensitive .
  • For search in UTF-8, use the function multiSearchAllPositionsUTF8.
  • For case-insensitive UTF-8 search, use the function multiSearchAllPositionsCaseInsensitiveUTF8.

Syntax

Parameters

  • haystack — string, in which substring will to be searched. String.
  • needle — substring to be searched. String.

Returned values

  • Array of starting positions in bytes (counting from 1), if the corresponding substring was found and 0 if not found.

Example

multiSearchAllPositionsUTF8

multiSearchFirstPosition(haystack, [needle1, needle2, …, needlen])

Так же, как и position , только возвращает оффсет первого вхождения любого из needles.

Для поиска без учета регистра и/или в кодировке UTF-8 используйте функции multiSearchFirstPositionCaseInsensitive, multiSearchFirstPositionUTF8, multiSearchFirstPositionCaseInsensitiveUTF8 .

multiSearchFirstIndex(haystack, [needle1, needle2, …, needlen])

Возвращает индекс i (нумерация с единицы) первой найденной строки needlei в строке haystack и 0 иначе.

Для поиска без учета регистра и/или в кодировке UTF-8 используйте функции multiSearchFirstIndexCaseInsensitive, multiSearchFirstIndexUTF8, multiSearchFirstIndexCaseInsensitiveUTF8 .

multiSearchAny(haystack, [needle1, needle2, …, needlen])

Возвращает 1, если хотя бы одна подстрока needlei нашлась в строке haystack и 0 иначе.

Для поиска без учета регистра и/или в кодировке UTF-8 используйте функции multiSearchAnyCaseInsensitive, multiSearchAnyUTF8, multiSearchAnyCaseInsensitiveUTF8 .

Во всех функциях multiSearch* количество needles должно быть меньше 2 8 из-за особенностей реализации.

match(haystack, pattern)

Проверка строки на соответствие регулярному выражению pattern. Регулярное выражение re2. Синтаксис регулярных выражений re2 является более ограниченным по сравнению с регулярными выражениями Perl (подробнее).
Возвращает 0 (если не соответствует) или 1 (если соответствует).

Обратите внимание, что для экранирования в регулярном выражении, используется символ \ (обратный слеш). Этот же символ используется для экранирования в строковых литералах. Поэтому, чтобы экранировать символ в регулярном выражении, необходимо написать в строковом литерале \ (два обратных слеша).

Регулярное выражение работает со строкой как с набором байт. Регулярное выражение не может содержать нулевые байты.
Для шаблонов на поиск подстроки в строке, лучше используйте LIKE или position, так как они работают существенно быстрее.

multiMatchAny(haystack, [pattern1, pattern2, …, patternn])

То же, что и match , но возвращает ноль, если ни одно регулярное выражение не подошло и один, если хотя бы одно. Используется библиотека hyperscan для соответствия регулярных выражений. Для шаблонов на поиск многих подстрок в строке, лучше используйте multiSearchAny , так как она работает существенно быстрее.

Длина любой строки из haystack должна быть меньше 2 32 байт, иначе бросается исключение. Это ограничение связано с ограничением hyperscan API.

multiMatchAnyIndex(haystack, [pattern1, pattern2, …, patternn])

То же, что и multiMatchAny , только возвращает любой индекс подходящего регулярного выражения.

multiMatchAllIndices(haystack, [pattern1, pattern2, …, patternn])

То же, что и multiMatchAny , только возвращает массив всех индексов всех подходящих регулярных выражений в любом порядке.

multiFuzzyMatchAny(haystack, distance, [pattern1, pattern2, …, patternn])

То же, что и multiMatchAny , но возвращает 1 если любой pattern соответствует haystack в пределах константного редакционного расстояния. Эта функция также находится в экспериментальном режиме и может быть очень медленной. За подробностями обращайтесь к документации hyperscan.

multiFuzzyMatchAnyIndex(haystack, distance, [pattern1, pattern2, …, patternn])

То же, что и multiFuzzyMatchAny , только возвращает любой индекс подходящего регулярного выражения в пределах константного редакционного расстояния.

multiFuzzyMatchAllIndices(haystack, distance, [pattern1, pattern2, …, patternn])

То же, что и multiFuzzyMatchAny , только возвращает массив всех индексов всех подходящих регулярных выражений в любом порядке в пределах константного редакционного расстояния.

multiFuzzyMatch* функции не поддерживают UTF-8 закодированные регулярные выражения, и такие выражения рассматриваются как байтовые из-за ограничения hyperscan.

Чтобы выключить все функции, использующие hyperscan, используйте настройку SET allow_hyperscan = 0; .

extract(haystack, pattern)

Извлечение фрагмента строки по регулярному выражению. Если haystack не соответствует регулярному выражению pattern, то возвращается пустая строка. Если регулярное выражение не содержит subpattern-ов, то вынимается фрагмент, который подпадает под всё регулярное выражение. Иначе вынимается фрагмент, который подпадает под первый subpattern.

extractAll(haystack, pattern)

Извлечение всех фрагментов строки по регулярному выражению. Если haystack не соответствует регулярному выражению pattern, то возвращается пустая строка. Возвращается массив строк, состоящий из всех соответствий регулярному выражению. В остальном, поведение аналогично функции extract (по прежнему, вынимается первый subpattern, или всё выражение, если subpattern-а нет).

extractAllGroupsHorizontal

Разбирает строку haystack на фрагменты, соответствующие группам регулярного выражения pattern . Возвращает массив массивов, где первый массив содержит все фрагменты, соответствующие первой группе регулярного выражения, второй массив - соответствующие второй группе, и т.д.

Функция extractAllGroupsHorizontal работает медленнее, чем функция extractAllGroupsVertical.

Синтаксис

Аргументы

  • haystack — строка для разбора. Тип: String.
  • pattern — регулярное выражение, построенное по синтаксическим правилам re2. Выражение должно содержать группы, заключенные в круглые скобки. Если выражение не содержит групп, генерируется исключение. Тип: String.

Возвращаемое значение

Если в строке haystack нет групп, соответствующих регулярному выражению pattern , возвращается массив пустых массивов.

Пример

Смотрите также

extractAllGroupsVertical

Разбирает строку haystack на фрагменты, соответствующие группам регулярного выражения pattern . Возвращает массив массивов, где каждый массив содержит по одному фрагменту, соответствующему каждой группе регулярного выражения. Фрагменты группируются в массивы в соответствии с порядком появления в исходной строке.

Синтаксис

Аргументы

  • haystack — строка для разбора. Тип: String.
  • pattern — регулярное выражение, построенное по синтаксическим правилам re2. Выражение должно содержать группы, заключенные в круглые скобки. Если выражение не содержит групп, генерируется исключение. Тип: String.

Возвращаемое значение

Если в строке haystack нет групп, соответствующих регулярному выражению pattern , возвращается пустой массив.

Пример

Смотрите также

like(haystack, pattern), оператор haystack LIKE pattern

Проверка строки на соответствие простому регулярному выражению.
Регулярное выражение может содержать метасимволы % и _ .

% обозначает любое количество любых байт (в том числе, нулевое количество символов).

_ обозначает один любой байт.

Для экранирования метасимволов, используется символ \ (обратный слеш). Смотрите замечание об экранировании в описании функции match.

Для регулярных выражений вида %needle% действует более оптимальный код, который работает также быстро, как функция position .
Для остальных регулярных выражений, код аналогичен функции match.

notLike(haystack, pattern), оператор haystack NOT LIKE pattern

То же, что like, но с отрицанием.

ilike

Нечувствительный к регистру вариант функции like. Вы можете использовать оператор ILIKE вместо функции ilike .

Синтаксис

Аргументы

  • haystack — входная строка. String.
  • pattern — если pattern не содержит процента или нижнего подчеркивания, тогда pattern представляет саму строку. Нижнее подчеркивание ( _ ) в pattern обозначает любой отдельный символ. Знак процента ( % ) соответствует последовательности из любого количества символов: от нуля и более.

Некоторые примеры pattern :

Возвращаемые значения

  • Правда, если строка соответствует pattern .
  • Ложь, если строка не соответствует pattern .

Пример

Смотрите также

ngramDistance(haystack, needle)

Вычисление 4-граммного расстояния между haystack и needle : считается симметрическая разность между двумя мультимножествами 4-грамм и нормализуется на сумму их мощностей. Возвращает число float от 0 до 1 – чем ближе к нулю, тем больше строки похожи друг на друга. Если константный needle или haystack больше чем 32КБ, кидается исключение. Если некоторые строки из неконстантного haystack или needle больше 32КБ, расстояние всегда равно единице.

Для поиска без учета регистра и/или в формате UTF-8 используйте функции ngramDistanceCaseInsensitive, ngramDistanceUTF8, ngramDistanceCaseInsensitiveUTF8 .

ngramSearch(haystack, needle)

То же, что и ngramDistance , но вычисляет несимметричную разность между needle и haystack – количество n-грамм из needle минус количество общих n-грамм, нормированное на количество n-грамм из needle . Чем ближе результат к единице, тем вероятнее, что needle внутри haystack . Может быть использовано для приближенного поиска.

Для поиска без учета регистра и/или в формате UTF-8 используйте функции ngramSearchCaseInsensitive, ngramSearchUTF8, ngramSearchCaseInsensitiveUTF8 .

Для случая UTF-8 мы используем триграммное расстояние. Вычисление n-граммного расстояния не совсем честное. Мы используем 2-х байтные хэши для хэширования n-грамм, а затем вычисляем (не)симметрическую разность между хэш таблицами – могут возникнуть коллизии. В формате UTF-8 без учета регистра мы не используем честную функцию tolower – мы обнуляем 5-й бит (нумерация с нуля) каждого байта кодовой точки, а также первый бит нулевого байта, если байтов больше 1 – это работает для латиницы и почти для всех кириллических букв.

countMatches(haystack, pattern)

Возвращает количество совпадений, найденных в строке haystack , для регулярного выражения pattern .

Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.

1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.

2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().

Если необходимо преобразовать символы строки к нижнему регистру используется функция LOWER().

Функция INITCAP преобразовывает каждый первый символ слова к верхнему регистру, а все остальные символы к нижнему при условии, что символ-разделитель между словами пробел.

вернет строку String1 String2.

3) Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая – все концевые, а третья все начальные и концевые.

SELECT LTRIM(‘ str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2 ’) FROM DUAL вернет строку str2,
SELECT TRIM(‘ str3 ’) FROM DUAL вернет строку str3.

4) Функция замены части строки другой строкой REPLACE(исходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для большей ясности рассмотрим пример, в некотором текстовом поле таблицы хранится число. Причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,». Для этого воспользуемся функцией REPLACE следующим образом. REPLACE(field1, ’.’, ’,’) и все символы «.» в поле field будут заменены на символ «,».

SELECT REPLACE(‘My_string’,’_’,’@’) FROM DUAL вернет строку My@string.

5) Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число. TO_DATE(строка, формат_даты) преобразует строку в дату определенного формата.

SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM DUAL вернет число 12345,
SELECT TO_DATE(’01.01.2010’,’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.

6) Функция определения вхождения подстроки в строку INSTR(исходная_строка, подстрока, номер_символа). Даная функция позволяет определять номер символа в исходной строке с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор

То есть оператор SELECT выведет только те записи из таблицы TABLE1 где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.

SELECT INSTR(‘Small string’, ‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR(‘Small string’, ‘String’, 1) FROM DUAL вернет значение 0.

7) Функция выделения в исходной строке подстроки SUBSTR(исходная_строка, номер_начального_символа, количество_символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую и при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().

Конечно для переноса данных необходимо воспользоваться оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет рассмотренный пример.
SELECT SUBSTR(‘My_string’, 4, 3) FROM DUAL вернет строку str.

Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию
SELECT SUBSTR(My_string, INSTR(My_string, ‘,’, 1), LENGTH(My_string)- INSTR(My_string, ‘,’, 1)+1) FROM DUAL.
Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.

8) Для определения кода символа используется функция ASCII(строка), которая возвращает код 1 символа строки. Например

SELECT ASCII(W) FROM DUAL вернет значение 87.

9) Обратная функция преобразования кода символа в символ CHR(число).

SELECT CHR(87) FROM DUAL вернет символ W.

Функции для работы с числами в Oracle.

В СУБД Oracle имеется ряд функций для работы с числами. К ним относятся функции возведение числа в степень POWER(), округление ROUND() и т. д.

1) Функция ABS(число) возвращает абсолютное значение аргумента.
SELECT ABS(-3) FROM DUAL вернет значение 3.

2) Функция CEIL(число) возвращает наименьшее целое, большее или равное переданному параметру.
SELECT CEIL(4.5) FROM DUAL вернет значение 5.

3) Функция FLOOR(число) возвращает наибольшее целое, меньшее или равное переданному параметру.
SELECT FLOOR(3.8) FROM DUAL вернет значение 3.

4) Функция MOD(число_1, число_2) возвращает остаток от деления первого параметра на второй.
SELECT MOD(5, 3) FROM DUAL вернет значение 2. Примечание. Если второй параметр равен 0, то функция возвращает первый параметр.

5) Функция округления ROUND(число_1, число_2). Округляет первый переданный параметр до количества разрядов, переданного во втором параметре. Если второй параметр не указан, то он принимается равным 0, то есть округление производится до целого значения. Примеры
SELECT ROUND(101.34) FROM DUAL вернет значение 101,
SELECT ROUND(100.1268, 2) FROM DUAL вернет значение 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL вернет значение 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL вернет значение -100.12.

6) Функция усечения значения TRUNC(число_1, число_2). Возвращает усеченное значение первого параметра до количества десятичных разрядов, указанного во втором параметре. Примеры
SELECT TRUNC(150.58) FROM DUAL вернет значение 150
SELECT TRUNC(235.4587, 2) FROM DUAL вернет значение 235.45
SELECT TRUNC(101.23, -1) FROM DUAL вернет значение 100

7) В СУБД Oracle имеется ряд тригонометрических функций SIN(число), COS(число), TAN(число) и обратные им ACOS(число), ASIN(число), ATAN(число). Они возвращают значение соответствующей названию тригонометрической функции. Для прямых функции параметром является значение угла в радианах, а для обратных – значение функции. Примеры
SELECT COS(0.5) FROM DUAL вернет значение 0.877582561890373
SELECT SIN(0.5) FROM DUAL вернет значение 0.479425538604203
SELECT TAN(0.5) FROM DUAL вернет значение 0.546302489843791
SELECT ACOS(0.5) FROM DUAL вернет значение 1.0471975511966
SELECT ASIN(0.5) FROM DUAL вернет значение 0.523598775598299
SELECT ATAN(0.5) FROM DUAL вернет значение 0.463647609000806

8) Гиперболические функции. SINH(число),
COSH(число), TANH(число). SINH() возвращает гиперболический синус переданного параметра, COSH() возвращает гиперболический косинус переданного параметра, TANH() возвращает гиперболический тангенс переданного параметра. Примеры
SELECT COSH(0.5) FROM DUAL вернет значение 1.12762596520638
SELECT SINH(0.5) FROM DUAL вернет значение 0.521095305493747 SELECT TANH(0.5) FROM DUAL вернет значение 0.46211715726001

9) Функция возведения в степень POWER(число_1, число_2). Примеры
SELECT POWER(10, 2) FROM DUAL вернет значение 100
SELECT POWER(100, -2) FROM DUAL вернет значение 0.0001

10) Логарифмические функции. LN(число) возвращает натуральный логарифм переданного параметра, LOG(число_1, число_2) возвращает логарифм второго переданного параметра по основанию, переданному первом параметре. Причем первый параметр должен быть больше нуля и не равен 1. Примеры
SELECT LN(5) FROM DUAL вернет значение 1.6094379124341
SELECT LOG(10, 3) FROM DUAL вернет значение 0.477121254719662

11) Функция извлечения квадратного корня SQRT(число). Пример
SELECT SQRT(4) FROM DUAL вернет значение 2.

12) Функция возведение числа е в степень EXP(число). Пример
SELECT EXP(2) FROM DUAL вернет значение 7.38905609893065.

Функции для работы с датами в Oracle

На практике очень часто необходимо анализировать данные в виде дат, производить некоторые операции над ними, изменять формат. Все эти операции уже реализованы в виде встроенных функций. Рассмотрим самые основные из них.

1) ADD_MONTHS(дата, количество_месяцев) возвращает дату, отстоящую от даты, переданной в первом параметре на количество месяцев, указанном во втором параметре. Примеры
SELECT ADD_MONTHS(’01-JAN-2010’, 2) FROM DUAL вернет дату ’01.03.2010’
SELECT ADD_MONTHS(’01-JAN-2010’, -3) FROM DUAL вернет дату ’01.10.2009’
SELECT ADD_MONTHS(’30-JAN-2010’, 1) FROM DUAL вернет дату ’28.02.2010’

2) Для определения текущей даты и времени применяется функция SYSDATE. Область применения данной функции намного шире чем может показаться на первый взгляд. В первую очередь это контроль за вводом данных в БД. Во многих таблицах выделяется отдельное поля для сохранения даты последнего внесения изменений. Также очень удобно контролировать некие входные параметры для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо даты данная функция возвращает еще и время с точностью до секунд. Пример
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010 14:51:20’

3) Если необходимо определить последний день месяца, то для этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для определения количества дней, оставшихся в месяце.
SELECT LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено количество дней от текущей даты до конца месяца. Пример
SELECT LAST_DAY(’15-FEB-2010’) FROM DUAL вернет дату ’28.02.2010’.

4) Функция для определения количества месяцев между датами MONTHS_BETWEEN(дата_1, дата_2). Примеры
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’01-JAN-2010’) FROM DUAL вернет значение -6
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’10-JAN-2010’) FROM DUAL вернет значение -6.29032258064516.
Примечание. Если дни месяцев совпадают, то функция возвращает целое число, в противном случае результат будет дробным, причем количество дней в месяце будет принято 31.

5) Функция NEXT_DAY(дата, день_недели) позволяет определить следующую дату от даты, переданной в первом параметре, которая соответствует дню недели, переданном во втором параметре. Пример
SELECT NEXT_DAY(’01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть следующий понедельник после 1 июля 2009 наступил 6 числа.

Значение параметраДень недели
monПонедельник
tueВторник
wedСреда
thuЧетверг
friПятница
satСуббота
sunвоскресенье

6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.

ФорматЕдиница округления
СС, SCCВек
SYYYY, YYYY, YEARГод
QКвартал
MM, MONTHМесяц
WWТот же день недели, что и первый день года
WТот же день недели, что и первый день месяца
DD, JДень
Day, DYПервый день недели
HH, HH12, HH24Час
MIМинута

7) Усечение даты. Функция TRUNC(дата, формат). Также как и рассмотренная выше может не иметь второго параметра. В таком случае усечение будет производиться до ближайшего дня. Примеры
SELECT TRUNC(SYSDATE) FROM DUAL вернет дату ’22.05.2010’
SELECT TRUNC(SYSDATE, ‘WW’) FROM DUAL вернет дату ’01.05.2010’
SELECT TRUNC(SYSDATE, ‘Day’) FROM DUAL вернет дату ‘16.05.2010’.

Функции преобразования данных в Oracle

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

1) TO_CHAR(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.

ФорматОписание формата
DДень недели
DDДень месяца
DDDДень года
MMНомер месяца
MONСокращенное название месяца
MONTHПолное название месяца
QКвартал
YY, YYY, YYYYГод
HH, HH12, HH24Час
MIМинут
SSСекунда

Таблица значений форматов для преобразования числа в строку.

ФорматОписание формата
99D9 Указание позиции разделителя десятичной точки. Число девяток соответствует максимальному количеству цифр
999G99Указание позиции группового разделителя
99,999Возвращает запятую в указанной позиции
99.999Возвращает точку в указанной позиции
99V9999Возвращает значение умноженное на 10 в степени n, где n число девяток после V.
0999Возвращает ведущие нули, а не пробелы
9990Возвращает конечные нули, а не пробелы
9.99EEEEВозвращает число в экспоненциальной форме
RMВозвращает число в римской системе исчисления

SELECT TO_CHAR(SYSDATE, ‘D-MONTH-YY’) FROM DUAL вернет строку ‘7-MAY -10’
SELECT TO_CHAR(SYSDATE, ‘DDD-MM-YYYY’) FROM DUAL вернет строку ‘142-05-2010’
SELECT TO_CHAR(SYSDATE, ‘Q-D-MM-YYY’) FROM DUAL вернет строку ‘2-7-05-010’
SELECT TO_CHAR(1050, ‘9.99EEEE) FROM DUAL вернет строку ‘ 1.050E+03’
SELECT TO_CHAR(1400, ‘9999V999’) FROM DUAL вернет строку ‘1400000’
SELECT TO_CHAR(48, ‘RM’) FROM DUAL вернет строку ‘ XLVIII’

2) Функция преобразования строки в дату TO_DATE(строка, формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу несколько примеров использования данной функции. Примеры
SELECT TO_DATE(’01.01.2010’, ‘DD.MM.YYYY’) FROM DUAL вернет дату ‘01.01.2010’
SELECT TO_DATE(’01.JAN.2010’, ‘DD.MON.YYYY’) FROM DUAL вернет дату ‘01.01.2009’
SELECT TO_DATE(’15-01-10’, ‘DD-MM-YY’) FROM DUAL вернет дату ‘15.01.2010’.

3) Функция преобразования строки в числовое значение TO_NUMBER(строка, формат). Самые распространенные значения форматов перечислены в таблице, поэтому рассмотрим применение данной функции на примерах. Примеры
SELECT TO_NUMBER(‘100’) FROM DUAL вернет число 100
SELECT TO_NUMBER(‘0010.01’, ’9999D99’) FROM DUAL вернет число 10.01
SELECT TO_NUMBER('500,000','999G999') FROM DUAL вернет число 500000.

Функция Oracle REGEXP_COUNT используется для подсчета количества раз, когда шаблон встречается в строке. Возвращает целое число, указывающее количество вхождений шаблона. Если совпадений не найдено, функция возвращает 0.

Синтаксис:

Параметры:

название Описание Типы данных
source_char Строка для поиска. CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.
шаблон шаблон является регулярным выражением. CHAR, VARCHAR2, NCHAR или NVARCHAR2.
позиция Необязательный. Позиция является положительным целым числом, указывающим символ source_char, с которого начинается поиск. Если опущено, по умолчанию используется значение 1, которое является первой позицией в строке.
match_param Необязательный. Это позволяет вам изменять поведение соответствия для функции REGEXP_COUNT.

Вот список операторов форматирования шаблона:

оператор Описание
/ д Цифровой символ
/ D Нецифровый персонаж.
/ ш Слово персонажа.
/ W Несловесный персонаж.
/ s Пробельный символ.
/ S Не пробельный символ.
/ A Соответствует только в начале строки или перед символом новой строки в конце строки.
/ Z Соответствует только в конце строки.
*? Соответствует предыдущему элементу шаблона 0 или более раз (не допускается).
+? Соответствует предыдущему элементу шаблона 1 или более раз (не разрешено).
?? Соответствует предыдущему элементу шаблона 0 или 1 раз (не разрешено).
? Соответствует предыдущему элементу шаблона ровно n раз (нон-жадность).
? Соответствует предыдущему элементу шаблона по крайней мере n раз (не разрешено).
? Соответствует предыдущему элементу шаблона, по крайней мере, n, но не более, чем mtimes (nongreedy).

Вот список операторов регулярных выражений и метасимволов:

Вот список значений для match_param:

Относится к

Oracle 12c, Oracle 11g

Примеры: функция Oracle REGEXP_COUNT

В следующем примере будет возвращено количество раз, когда слово «The» появляется в строке.

Предыдущая: ДЛИНА
Далее: REGEXP_INSTR

UPPER, LOWER

Данные функции уже описывались раньше.

  • UPPER : приводит строку к верхнему регистру
  • LOWER : приводит строку к нижнему регистру

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

Конкатенация строк

Конкатенация - это "склейка" строк. Т.е., если у нас есть 2 строки - "Новый", "фотоаппарат", то результатом конкатенации будет строка "Новый фотоаппарат".

Для склейки строк в Oracle используется оператор || .


Поиск подстроки

Для того, чтобы найти вхождение одной строки в другую, используется функция INSTR . Она возвращает позицию вхождения одной строки в другую. Если вхождения не обнаружено, то в качестве результата будет возвращён 0.

Следующий запрос возвращает позицию, начиная с которой в заголовках записей пользователей встречается символ восклицательного знака:


Как видно, для тех заголовков, которые не содержат восклицательный знак, функция INSTR вернула 0.

В функции INSTR можно задавать позицию, начиная с которой следует производить поиск вхождения:


Данный запрос вернет позицию буквы о в заголовках записей, но поиск будет производить лишь начиная с 3-го символа заголовка.

Так, в строке "Новый фотоаппарат" мы получили результат 8, хотя буква о есть и раньше - на второй позиции.

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


Также можно указать, какое по счету совпадение нужно искать(4-ый параметр в функции INSTR ):


Подобие строк. Like

Для рассмотрения этой темы будем использовать данные из части про сортировку.

Предположим, нам понадобилось посмотреть, какие чаи есть у нас в меню. В данном примере единственный способ, которым мы можем определить, что блюдо является чаем - это проверить, содержится ли слово чай в наименовании.

Но оператор сравнения здесь не подойдет, так как он вернет лишь те строки, которые будут полностью совпадать со строкой Чай.

Перед рассмотрением примера добавим в таблицу меню немного чайных блюд:

Гениальные маркетологи решили, что будут добавлять по одному символу в конце слова чай для обозначения его крепости - "чай%" - совсем слабенький, "чай+" взбодрит с утра, а с "чаем!" можно забыть про сон на ближайшие сутки. Не будем задумываться, почему именно так, а просто примем это как есть.

Итак, первый пример использования LIKE :


Как видно, были получены все блюда, наименования которых начиналось с последовательности символов, составляющей слово Чай. Символ "%" в условии LIKE соответствует любой последовательности символов. Т.е. предыдущий запрос можно было читать так: "Получить все блюда, первые символы наименований которых составляют слово Чай, а после этих символов следует последовательность из любых символов в любом количестве, мне не важно". Кстати, в результат не попал зеленый чай - первые 3 символа наименования у него равны "Зел", но никак не "Чай".

Если не указывать символ "%", то запрос не вернет никаких данных:


При задании шаблонов в LIKE можно использовать следующие символы соответствия:

  • "%"(знак процента). Ему соответствует 0 или больше символов в значении.
  • "_"(нижнее подчеркивание). Ему соответствует ровно один символ в значении.

Получим все чаи, названия которых придумали маркетологи(а это любой 1 символ после слова "чай"):


Также, как и при обычном сравнении, учитывается регистр строк. Следующий запрос не вернет никаких данных, т.к. нет блюд, начинающихся со строки "чай", есть только блюда, начинающиеся на "Чай"(первая буква заглавная):


Получим только зеленый чай:


Здесь символ процента был перемещен перед словом "чай", что означает: "Любая последовательность символов(или их отсутствие), заканчивающаяся словом чай".

А для того, чтобы получить список всех блюд, в наименовании которых содержится слово "чай", можно написать следующий запрос:


Выражение ESCAPE в LIKE

Перед рассмотрением выражения опять добавим немного данных в таблицу dishes :

Перед нами стоит задача: получить список кофейных блюд, содержащих кофеин.

Можно выделить некоторый список признаков, по которым мы сможем определить, что кофе с кофеином:

  • Наименование начинается со слова "Кофе"
  • Если кофе с кофеином, то в скобках указывается его процентное содержание в виде "n% кофеина", где n - некоторое число.

На основании этих заключений можно написать следующий запрос:


В чем проблема, должно быть понятно - в том, что символ "%" в условии LIKE обозначает совпадение с 0 или больше любых символов.

Для того, чтобы учитывать непосредственно символ "%" в строке, условие LIKE немного видоизменяется:


Здесь после ключевого слова escape мы указываем символ, который будет экранирующим, т.е. если перед символами % будет стоять символ \ , то он будет рассматриваться как совпадение с одним символом % , а не как совпадение 0 и больше любых символов.

Приведение к верхнему регистру. INITCAP

Функция INITCAP делает первую букву каждого слова заглавной, оставляя остальную часть слова в нижнем регистре.



Если строка состоит из нескольких слов, то в каждом из этих слов первая буква будет заглавной, а остальные - прописными.

Замена подстроки. REPLACE

Для замены подстроки в строке используется функция REPLACE . Данная функция принимает 3 параметра, из них последний - не обязательный:

В случае, если не указать, на какую строку производить замену, то совпадения будут просто уделены из исходной строки.

Например, получим все "твиты" пользователя johndoe, но в заголовке поста заменим слово "фотоаппарат" заменим на слово "мыльница":


Удаление пробелов. TRIM

Есть 3 основных функции для удаления "лишних" пробелов из строки:

  • trim - удалить пробелы вначале и в конце строки
  • ltrim - удалить пробелы вначале строки (слева)
  • rtrim - удалить пробелы в конце строки (справа)

LPAD, RPAD

Эти функции используются, чтобы дополнить строку какими-либо символами до определенной длины.

LPAD (left padding) используется для дополнения строки символами слева, а RPAD (right padding) - для дополнения справа.


Первый параметр в этой функции - строка, которую нужно дополнить, второй - длина строки, которую мы хотим получить, а третий - символы, которыми будем дополнять строку. Третий параметр не обязателен, и если его не указывать, то строка будет дополняться пробелами, как в колонке n2_1 .

Читайте также: