Substr oracle описание: REGEXP_SUBSTR ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

REGEXP_SUBSTR ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать функцию REGEXP_SUBSTR Oracle/PLSQL с синтаксисом и примерами.

Описание

Функция Oracle/PLSQL REGEXP_SUBSTR является расширением функции SUBSTR. Эта функция, представленная в Oracle 10g, позволит вам извлечь подстроку из строки, используя сопоставление шаблонов регулярных выражений.

Синтаксис

Синтаксис функции Oracle/PLSQL REGEXP_SUBSTR :

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

Параметры или аргументы

string
Строка для поиска. Это могут быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

pattern
Шаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:

start_position
Необязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.

nth_appearance
Необязательный. Это n-й вид шаблона в строке. Если этот параметр опущен, по умолчанию он равен 1, который является первым вхождением шаблона в строке. Если вы укажете 0 для этого параметра, все вхождения шаблона в строке будут заменены.
match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_REPLACE. Это может быть комбинацией следующих значений:

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

• Функция REGEXP_SUBSTR возвращает строковое значение.
• Если функция REGEXP_SUBSTR не обнаруживает какого-либо вхождения шаблона, она возвращает NULL.

Примечание

• Если для параметра match_parameter имеются конфликтующие значения, функция REGEXP_SUBSTR будет использовать последнее значение.
• См. Также функцию SUBSTR.

Применение

Функция REGEXP_SUBSTR может использоваться в следующих версиях Oracle / PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g

Пример совпадения в словах

Начнем с извлечения первого слова из строки.
Например:

SELECT REGEXP_SUBSTR (‘Google is a great search engine.’, ‘(\S*)(\s)’)
FROM dual;
—Результат: ‘Google ‘

Этот пример вернет ‘Google ‘, потому что он будет извлекать все символы без пробелов, как указано (\S*), а затем первый символ пробела, заданный (\s). Результат будет включать как первое слово, так и пробел после слова.

Если вы не хотите включать пробел в результат, то изменим наш пример следующим образом:

SELECT REGEXP_SUBSTR (‘Google is a great search engine.’, ‘(\S*)’)
FROM dual;
—Результат: ‘Google’

Этот пример вернет ‘Google’ без пробела в конце.

Если нам необходимо найти второе слово в строке, то изменим нашу функцию следующим образом:

SELECT REGEXP_SUBSTR (‘Google is a great search engine.’, ‘(\S*)(\s)’, 1, 2)
FROM dual;
—Результат: ‘is ‘

Этот пример вернет ‘is ‘ с пробелом в конце строки.
Если нам необходимо найти четвертое слово в строке, мы изменим нашу функцию следующим образом:

SELECT REGEXP_SUBSTR (‘Google is a great search engine.’, ‘(\S*)(\s)’, 1, 4)
FROM dual;
—Результат: ‘great ‘

Этот пример вернет ‘great ‘ с пробелом в конце строки.

Пример совпадения цифр

Рассмотрим, как мы будем использовать функцию REGEXP_SUBSTR для сопоставления шаблону цифровых символов.
Например:

SELECT REGEXP_SUBSTR (‘2, 4, и 10 числа для примера’, ‘\d’)
FROM dual;
—Результат: ‘2 ‘

В этом примере будет извлечена первая цифра из строки, как указано в \d. В этом случае он будет соответствовать числу 2.

Мы могли бы изменить наш шаблон для поиска двузначного числа.
Например:

SELECT REGEXP_SUBSTR (‘2, 4, и 10 числа для примера’, ‘(\d)(\d)’)
FROM dual;
—Результат: ’10’

В этом примере будет выведено число, которое имеет две цифры, как указано в (\d)(\d). В этом случае он пропустит числовые значения 2 и 4 и вернет 10.

Рассмотрим, как мы будем использовать функцию REGEXP_SUBSTR со столбцом таблицы и искать двухзначное число.
Например:

SELECT REGEXP_SUBSTR (address, ‘(\d)(\d)’)
FROM contacts;

В этом примере мы собираемся извлечь первое двузначное значение из поля address в таблице contacts.

Пример сопоставления несколько альтернатив.

Следующий пример, который мы рассмотрим, включает использование | шаблон. | шаблон используется как «ИЛИ», чтобы указать несколько альтернатив.
Например:

SELECT REGEXP_SUBSTR (‘AeroSmith’, ‘a|e|i|o|u’)
FROM dual;
—Результат: ‘e’

Этот пример вернет ‘e’, потому что он ищет первую гласную (a, e, i, o или u) в строке. Поскольку мы не указали значение match_parameter, функция REGEXP_SUBSTR выполнит поиск с учетом регистра, что означает, что ‘A’ в ‘AeroSmith’ сопоставляться не будет.

Чтобы выполнить поиск без учета регистра изменим наш запрос следующим образом:

SELECT REGEXP_SUBSTR (‘AeroSmith’, ‘a|e|i|o|u’, 1, 1, ‘i’)
FROM dual;

—Результат: ‘A’

Теперь, поскольку мы предоставили match_parameter = ‘i’, запрос в качестве результата вернет ‘A’. На этот раз ‘A’ в ‘AeroSmith’ будет сопоставляться.

Теперь рассмотри, как вы будете использовать эту функцию со столбцом.
Итак, допустим, у нас есть таблица contact со следующими данными:

Теперь давайте запустим следующий запрос:

SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, ‘a|e|i|o|u’, 1, 1, ‘i’) AS «First Vowel»
FROM contacts;

Результаты, которые будут возвращены запросом:

Пример совпадений на основе параметра nth_occurrence

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

Первое вхождение

Рассмотрим, как извлечь первое вхождение шаблона в строку.
Например:

SELECT REGEXP_SUBSTR (‘AeroSmith’, ‘a|e|i|o|u’, 1, 1, ‘i’)
FROM dual;
—Результат: ‘A’

Этот пример вернет ‘A’, потому что он извлекает первое вхождение гласного (a, e, i, o или u) в строке.

Второе вхождение

Затем мы выберем для второго вхождения шаблона в строку.
Например:

SELECT REGEXP_SUBSTR (‘AeroSmith’, ‘a|e|i|o|u’, 1, 2, ‘i’)
FROM dual;
—Результат: ‘e’

Этот пример вернет ‘e’, потому что он извлекает второе вхождение гласного (a, e, i, o или u) в строке.

Третье вхождение

Например:

SELECT REGEXP_SUBSTR (‘AeroSmith’, ‘a|e|i|o|u’, 1, 3, ‘i’)
FROM dual;
—Результат: ‘o’

Этот пример вернет ‘o’, потому что он извлекает третье вхождение гласного (a, e, i, o или u) в строке.

Функция SUBSTRING — вырезание подстроки

Функция SUBSTRING вырезает и возвращает заданное количество
символов из строки.

Первым параметром функция принимает поле или строку, вторым параметром —
с какой позиции начинать вырезания (нумерация символов начинается с 1), третьем параметром —
сколько символов брать.

Третий параметр не является обязательным. Если он не указан,
текст будет вырезан с указанной
позиции и до конца строки.

См. также функцию MID,
которая также вырезает часть строки.

См. также функцию SUBSTRING_INDEX,
которая берет часть строки по указанному разделителю.

Синтаксис

Первый синтаксис:

SELECT SUBSTRING(поле, откуда_отрезать, сколько_символов_взять) FROM имя_таблицы WHERE условие

Альтернативный синтаксис:

SELECT SUBSTRING(поле FROM откуда_отрезать FOR сколько_символов_взять) FROM имя_таблицы WHERE условие

Третий параметр не обязателен, в этом случае текст будет вырезан с указанной
позиции и до конца строки:

SELECT SUBSTRING(поле, откуда_отрезать) FROM имя_таблицы WHERE условие

SELECT SUBSTRING(поле FROM откуда_отрезать) FROM имя_таблицы WHERE условие

Примеры

Все примеры будут по этой таблице texts, если не сказано иное:

Пример

В данном примере из строки вырезаются и возвращаются
6 символов, начиная с 5-го:

SELECT id, SUBSTRING(text, 5, 6) as text FROM texts

SQL запрос выберет следующие строки:

Запрос можно переписать в следующем виде:

SELECT id, SUBSTRING(text FROM 5 FOR 6) as text FROM texts

Пример

В данном примере возвращается вся строка до конца,
начиная с пятого символа:

SELECT id, SUBSTRING(text, 5) as text FROM texts

SQL запрос выберет следующие строки:

Запрос можно переписать в следующем виде:

SELECT id, SUBSTRING(text FROM 5) as text FROM texts

Встроенные функции Oracle

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

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

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

SELECT LENGTH(‘string ’) FROM DUAL вернет значение 7.

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

SELECT UPPER(‘string’) FROM DUAL вернет STRING.

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

SELECT LOWER(‘STrinG’) FROM DUAL

вернет string.

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

SELECT INITCAP(‘string1 string2’) FROM DUAL

вернет строку 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 * FROM TABLE1 WHERE INSTR(POST, ‘менеджер’, 1) > 0.

То есть оператор 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().

SELECT SUBSTR(TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR(TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR(TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1

Конечно для переноса данных необходимо воспользоваться оператором 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 числа.

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

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

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(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.

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

Примеры

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.

Функция SUBSTRING_INDEX | Трепачёв Дмитрий

Функция SUBSTRING_INDEX
возвращает подстроку из строки перед появлениям N вхождений разделителя.

Если N положителен, то возвращается все, что находится слева от последнего разделителя (считая слева).
Если N отрицателен, то возвращается все, что находится справа от последнего разделителя (считая справа).

См. также функцию LEFT,
которая вырезает символы с начала строки.

См. также функцию RIGHT,
которая вырезает символы с конца строки.

См. также функции
SUBSTRING и
MID
которые вырезают символы с любого места строки.

Синтаксис

SELECT SUBSTRING_INDEX(поле, разделитель, количество_разделителей)
	FROM имя_таблицы WHERE условие

Примеры

Все примеры будут по этой таблице texts, если не сказано иное:

Пример

В данном примере вернется подстрока перед первым вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', 1) as text FROM texts

SQL запрос выберет следующие строки:

Пример

В данном примере вернется подстрока перед вторым вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', 2) as text FROM texts

SQL запрос выберет следующие строки:

Пример

В данном примере вернется подстрока перед третьим вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', 3) as text FROM texts

SQL запрос выберет следующие строки:

Пример

В данном примере вернется подстрока перед первым с конца вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', -1) as text FROM texts

SQL запрос выберет следующие строки:

Пример

В данном примере вернется подстрока перед вторым с конца вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', -2) as text FROM texts

SQL запрос выберет следующие строки:

Пример

В данном примере вернется подстрока перед третьим с конца вхождением
разделителя ‘-‘:

SELECT *, SUBSTRING_INDEX(text, '-', -3) as text FROM texts

SQL запрос выберет следующие строки:

Использование символьных, строковых функций и функций работы с датой в SQL

Функции, которые мы обсудим в этой части обычно используют встроенные PL/SQL код, сгруппированный в пакеты и поставляемый Oracle. Некоторые обрабатывают численные, символьные значения и значения даты, другие преобразуют данные в различные типы данных. Функции могут использовать вложенные вызовы и некоторые функции предназначены для работы со значением NULL. Функции условия CASE и DECODE позволяют отображать различный результат в зависимости от значений данных, что предоставляет возможность ветвления в контексте SQL запроса

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

Определение функции

Функция – это программа, которая может принимать (но необязательно) входные параметры, выполнять какие-либо операции и возвращать значение-литерал. Функция возвращает только одно значение за вызов.

Три важных компонента формируют базис определения функции. Первый – это список входных параметров. Он определяет ноль или более параметров, которые могут передаваться функции для обработки. Эти параметры, или аргументы, могут быть необязательными (иметь значение по умолчанию) и быть разными типами данных. Второй компонент – это тип данных вовзращаемого результата. После выполнения, только одно значение предопределенного типа данных возвращается функцией. Третий компонент инкапсулирует детали обработки выполняемой функцией и содержит программный код, который работает с входными параметрами, производит вычисления и возвращает значение.

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

Вызовы функций могут быть вложенными, к примеру, как F1(x, y, F2(a, b), z), где функция F2 принимает два входных параметра и возвращает третий из четырёх параметров для функции F1. Функции могут работать с любыми типами данных: наиболее часто используемые это символьные и числовые данные, а также данные типа дата. Этими параметрами функции могут быть столбцами или выражениями.

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

Типы функций

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

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

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

Следующий запрос выбирает два столбца из таблицы REGIONS и выражение использующее функцию LENGTH и столбец REGION_NAME

select region_id, region_name, length(region_name) from regions;

Длина значения столбца REGION_NAME рассчитывается для каждой из четырёх строк в таблице REGIONS; функция выполняется четыре раза, возвращая каждый раз значение-литерал.

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

Помимо использования функций в разделе SELECT строчные функции можно использовать в разделах WHERE и ORDER BY.

Функции, работающие с набором данных

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

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

Данные в таблицах могут заполняться из различных источников: программ, криптов и так далее. Не стоит полагаться что символьные данные будут вводиться в заранее определенном регистре. Строчные функции, изменяющие регистр предназначены для двух важных задач. Их можно использовать, во-первых, для изменения регистра данных при сохранении или выводе информации, либо в условиях WHERE для более гибкого поиска. Гораздо легче искать строку используя фиксированный регистр, вместро проверки всех комбинаций верхнего и нижнего регистра. Помните, что вызов функций не изменяет данные, которые хранятся в таблице. Они преобразуют данные результата запроса.

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

Функция LOWER

Функция LOWER заменяет все символы прописного регистра на эквивалентные символы строчного регистра. Синтакис функции LOWER(string). Рассмотрим пример запроса использующего эти функции

select lower(100+100), lower(‘SQL’), lower(sysdate) from dual

Преположим что текущая дата 17 декабря 2015 года. Результатом запроса будут строки ‘200’, ‘sql’ и ‘17-dec-2015’.  Численное выражение и дата неявно преобразуются в строку перед вызовом функции LOWER.

В следующем примере функция LOWER используется для поиска строк где буквы ‘U’ и ‘R’ в любом регистре идут друг за другом

select first_name, last_name, lower(last_name) from employees

where lower(last_name) like ‘%ur%’;

Можно написать аналогичный запрос без использования функции LOWER. Например так

select first_name, last_name from employees

where last_name like ‘%ur%’ or last_name like ‘%UR%’

or last_name like ‘%uR%’ or last_name like ‘%Ur%’

Этот запрос работает, но он слишком громоздкий, и количество операторов OR возрастает экспоненциально по мере увеличения строки.

Функция UPPER

Функция UPPER логическая противоположность функции LOWER и заменяет все строчные символы на их прописные эквиваленты. Синтаксис функции – UPPER(string). Рассмотрим пример

select * from countries where upper(country_name) like ‘%U%S%A%’;

Этот запрос выбирает строки из таблцы COUNTRIES где COUNTRY_NAME содержит буквы ‘U’, ‘S’, ‘A’ в любом регистре в этом порядке.

Функция INITCAP

Функция INITCAP часто используется для отображения данных. Первые символы каждого слова в строке преобразуются к верхнему регистру, все остальные символы преобразуются в строчные эквиваленты. Под словом подразумевается набор символов не содержащих пробелов и спецсимволов. Пробел, символ подчеркивания а также спецсимволы такие как знак процента, восклицательные знак, знак доллара расцениваются как разделители. Функция INITCAP принимает один параметр и синтаксис INITCAP(string). Следующий пример показывает пример использования функции INITCAP

select initcap(‘init cap or init_cap or init%cap’) from dual

Результатом этого запроса будет строка Init Cap Or Init_Cap Or Init%Cap

Использование функций работы со строками

Функции работы со строками одна из самых мощных возможностей, предоставляемых Oracle. Они очень полезны и понятны практически без детальных объяснений и очень часто используются разными программистами при обработке данных. Часто используются вложенные вызовы этих функций. Оператор конкатенации может использоваться вместо функции CONCAT. Функции LENGTH, INSTR, SUBSTR и REPLACE могут дополнять друг друга, так же как RPAD, LPAD и TRIM.

Функция CONCAT

Функция CONCAT объединяет два литерала, столбца или выражения для составление одного большого выражения. У функции CONCAT два входных параметра. Синтаксис функции CONCAT(string1, string2) где string1 и string2 могут быть литералом, столбцом или выражением результат которого символьный литерал. Следующий пример показывает использование функции CONCAT

select concat(‘Today is:’,SYSDATE) from dual

Второй параметр функции это функция SYSDATE, которая возвращает текущее системное время. Значение преобразуется в строку и к ней присоединяется первый параметр. Если текущая системная дата 17 Декабря 2015 года, то запрос вернёт строку ‘Today is:17-DEC-2015’.

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

select concat(‘Outer1 ‘, concat(‘Inner1′,’ Inner2′)) from dual;

У первой функции два параметра: первый параметр это литерал ‘Outer1 ‘, а второй параметра это вложенная функция CONCAT. Вторая функция принимает два параметра: литерал ‘Inner1’ и литерал ‘ Inner2’. Результатом выполнения этого запроса будет строка ‘Outer1 Inner1 Inner 2’. Вложенные функции расмотрим чуть позже.

Функция LENGTH

Функция LENGTH возвращает число символов которые составляют строку. Пробелы, табуляция и специальные символы учитываются функцией LENGTH. У функции один параметра и синтаксис LENGTH(string). Рассмотрим запрос

select * from countries where length(country_name) > 10;

Функция LENGTH используется для выбора тех стран у которых длина названия больше чем десять символов.

Функции RPAD и LPAD

Функции RPAD и LPAD возвращают строку фиксированной длины и при необходимости дополняют исходное значение определенным набором символов слева или справа. Символами используемые для добавления могут быть литерал, значение столбца, выражение, пробел (значение по умолчанию), табуляция и спец символы. Функции LPAD и RPAD принимают три входных параметра и синтаксис LPAD(s, n, p) и RPAD(s, n, p) где s – значение строки для обработки, n – количество символов результата и p – символы для добавления. Если используется LPAD, то символы p добавляются слева до достижения длины n. Если RPAD – то справа. Обратите внимание что если длина s больше чем длина n – то результатом будет первые n символов значения s. Рассмотрим запросы на рисунке 10-1

Рисунок 10-1 – Использование функций RPAD и LPAD

Первый запрос не изменяет данные и результат не очень читабельный по сравнению с результатом второго запроса. RPAD используется для добавления пробелов там где необходимо для first_name и last_name чтобы все значения были фиксированной длины в 18 символов, и LPAD используется для добавления пробелов в начало значения salary до достижения длины 6 символов.

Функция TRIM

Функция TRIM убирает символы и начала или окончания строки чтобы сделать её потенцильно короче. Функция принимает обязательный параметр и необязательный. Синтаксис функции TRIM([trailing|leading|both] trimstring from string). Параметр входная строка (s) обязательный. Следующие пункты перечисляют параметры

• TRIM(s) убираются пробелы в начале в к конце строки
• TRIM(trailing trimstring from s) убирает символы trimgstring в конце строки
• TRIM(leading trimstring from s) убирает символы trimgstring в начале строки
• TRIM(both trimstring from s) OR TRIM(trimstring from s) убирают все символы trimstring в начале и в конце строки

Запрос

select trim(both ‘*’ from ‘****Hidden****’),

trim(leading ‘*’ from ‘****Hidden****’),

trim(trailing ‘*’ from ‘****Hidden****’) from dual;

Вернёт “Hidden”, “Hidden****”, и “****Hidden”. Обратите внимание что, указав всего один символ, все символы убираются если они последовательно повторяются.

Функция INSTR

Функция INSTR ищет подстроку в строке. Возвращается число, обозначающее позицию откуда n-ное вхождение начинается, начиная с позиции поиска, относительно начала строки. Если подстрока не найдена в строке – возвращается 0.

У функции INSTR два параметра обязательных и два параметра необязательных. Синтаксис функции INSTR(source string, search string, [search start position], [n occurrence]). Значение по умолчанию для search start position=1 или другими словами начало строки source string. Значение по умолчанию для n occurrence=1 или первое вхождение. Рассмотрим несколько примеров

Query 1: select instr(‘1#3#5#7#9#’, ‘#’) from dual;

Query 2: select instr(‘1#3#5#7#9#’, ‘#’ ,5) from dual;

Query 3: select instr(‘1#3#5#7#9#’, ‘#’, 3, 4) from dual;

Первый запрос ищет первое вхождение хеш-тега в строке и возвращает значение 2. Второй запрос ищет хеш-тег в строке начиная с пятого символа и находит первое вхождение с 6 символа. Третий запрос ищет четвертое вхождение хеш-тега начиная с третьего символа и находит его в позиции 10.

Функция SUBSTR

Функция SUBSTR возвращает подстроку определённой длины из исходной строки начиная с определённой позиции. Если начальная позиция больше чем длина исходной строки – возвращается значение NULL. Если длины исходной строки недостаточно для получения значения необходимой длины начиная с определённой позиции, то возвращается часть строки с исходного символа до конца строки.

У функции SUBSTR три параметра, первые два обязательны и синтаксис SUBSTR(source string, start position, [number of characters]). Значение по умолчанию для characters to extract = разница между длиной source string и start position. Рассмотрим следующие примеры

Query 1: select substr(‘1#3#5#7#9#’, 5) from dual;

Query 2: select substr(‘1#3#5#7#9#’, 5, 3) from dual;

Query 3: select substr(‘1#3#5#7#9#’, -3, 2) from dual;

Запрос 1 возвращает подстроку начиная с позиции 5. Так как третий параметр не указан, количество символов равно длине исходной строки минус начальная позиция и будет равно шести. Первый запрос вернёт подстроку ‘5#7#9#’. Запрос два возвращает три символа начиная с пятого символа и строка результат будет ‘5#7’. Запрос три начинается с позиции минус три. Отрицательная начальная позиции говорит Oracle о том, что начальная позиция рассчитывается от конца строки. Таким образом начальная позиция будет длина строки минус три и равна 8. Третий параметр равен двум и возвращается значение ‘#9’.

Функция REPLACE

Функция REPLACE заменяет все вхождения искомого элемента на значение строки для подстановки. Если длина заменяемого элемента не равна длине элемента, на который происходит замена, длина получаемой строки будет отличной от исходной строки. Если искомая подстрока не найдена, строка возвращается без изменений. Доступно три параметра, два первых обязательные и синтаксис вызова REPLACE(source string, search element, [replace element]). Если явно не указать параметр replace element, то из исходной строки удаляются все вхождения search element. Другими словами, replace element равно пустой строке. Если все символы исходной строки заменяются пустым replace element возвращается NULL. Рассмотрим несколько запросов

Query 1: select replace(‘1#3#5#7#9#’,’#’,’->’) from dual

Query 2: select replace(‘1#3#5#7#9#’,’#’) from dual

Query 3: select replace(‘#’,’#’) from dual

Хеш в первом запрос обозначает символ для поиска и строка для замены ‘->’. Хеш появляется в строке пять раз и заменяется, получаем итоговую строку ‘1->3->5->7->9->’. Запрос 2 не указывает явно строку для замены. Значением по умолчанию является пустая строка и результатом будет ‘13579’. Запрос номер три вернёт NULL.

Использование численных функций

В Oracle доступно множество встроенных функций для работы с числами. Существенной разницой между численными функция и другими является то, что эти функции принимают параметрами только числа и возвращают только числа. Oracle предоставляет численные функции для работы с тригонометрическими, экспоненциальными и логарифмическими выражениями и со многими другими. Мы сфокусируемся на простых численных строчных функциях: ROUND, TRUNC и MOD.

Функция ROUND

Функция ROUND округляет число в зависимости от необходимой точности. Возвращаемое значение округляется либо в большую, либо в меньшую сторону, в зависимости от значения последней цифры в необходимом разряде. Если значение точности n, то цифра, которая будет округляться будет на позиции n после запятой, а значение будет зависеть от цифры на позиции (n+1). Если значение точности отрицательное, то все цифры после разряда n слева от запятой будут 0, а значение n будет зависеть от n+1. Если значение цифры от которой зависит округление больше или равно 5, то округление происходит в большую сторону, иначе в меньшую.

Функция ROUND принимает два входных параметра и синтаксис ROUND(source number, decimal precision). Source number может быть любым числом. Параметр decimal precision определяет необходимую точность и необязателен. Если этот параметр не указан, значение по умолчанию будет 0, что обозначает необходимость округления до ближайшего целого числа.

Рассмотрим таблицу 10-1 для числа 1601.916. Отрицательные значения точности находятся слева от точки (целая часть), когда положительные считаются вправо от точки (дробная часть).

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

Query 1: select round(1601.916, 1) from dual;

Query 2: select round(1601.916, 2) from dual;

Query 3: select round(1601.916, -3) from dual;

Query 4: select round(1601.916) from dual;

Первый запрос использует параметр точности равные единице, что означает что число будет округлено до ближайшей десятой. Так как значение сотой части равно единице (меньше чем 5), то происходит округление в меньшую сторону и возвращается значение 1601.9. Точность второго запроса равна двойке, таким образом значение окружается до сотой. Так как значение тысячной части равно 6 (что больше 5), то значение сотой части округляется вверх и возвращается значение 1601.92. Значение параметра точности в третьем запросе равно минус трём. Так как значение отрицательное, это значит, что округление будет происходить, основываясь на значении третьей позиции слева от точки, во втором разряде (сотни), и значение 6. Так как 6 больше пяти, то происходит округление вверх и возвращается значение 2000. Запрос 4 вызывает функцию без параметра точности. Это означает что число округляется до ближайшего целого. Так как десятая часть равна 9, то значение округляется в большую сторону и возвращется значение 1602.

Численная функция TRUNC

Функия TRUNC сокращает значение числа основываясь на значение параметра точности. Сокращение отличается от округления тем, что при сокращении лишняя часть просто отрезается и не происходит никаких изменений остальных цифр числа. Если значение точности отрицательное, то входное значение сокращается на позиции слева от запятой. Синтаксис функции TRUNC(source number, decimal precision). Параметром source number может быть любое число и этот параметр обязателен. Параметр decimal precision определяет позицию округления и не обязателен, значением по умолчанию будет ноль, что означает сокращение до целого числа.

Если значение decimal precision равно одному, то число сокращается до десятых, если два, то до сотых и так далее. Рассмотрим несколько примеров использования этой функции

Query 1: select trunc(1601.916, 1) from dual;

Query 2: select trunc(1601.916, 2) from dual;

Query 3: select trunc(1601.916, -3) from dual;

Query 4: select trunc(1601.916) from dual;

В запросе 1 используется точность равная единице, что значит сокращение значения до десятых и возвращается значение 1601.9. Точность во втором запросе равна двум, исходное значение сокращается до сотых и возвращается значение 1601.91. Обратите внимание что получаемое значение будет отличаться от значения, возвращаемого функцией ROUND с такими же параметрами, так как при вызове ROUND произойдёт округление в большую сторону (6 больше 5). В запросе номер три используется отрицательное число как значение параметра точности. Позиция три слева от запятой означает что сокращение будет до третьего разряда (сокращаются сотни) как показано в таблице 10-1 и возвращаемое значение будет 1000. И наконец в четвертом запросе явно неуказано значение точности и сокращается дробная часть исходного числа. Результатом будет 1601.

Функция MOD

Функция MOD возвращает остаток от деления. Два числа, делимое (число которое делится) и делитель (число на которое делится) определяются как параметры и вычисляется операция деления. Если делимое делится на делитель нацело, то возвращается ноль, так как нет остатка. Если делитель ноль, то не происходит ошибки деления на ноль, а возвращается делимое. Если делитель больше чем делимое, возвращается делимое.

У функции MOD два входные параметра и синтаксис MOD(dividend, divisor). Параметры dividend и divisor могут быть численными литералами, столбцами или выражениями и могут быть положительными или отрицательными. Следующие примеры показывают использование этой функции

Query 1: select mod(6, 2) from dual

Query 2: select mod(5, 3) from dual

Query 3: select mod(7, 35) from dual

Query 4: select mod(5.2, 3) from dual

В запросе один 6 делится на два нацело без остатка и возвращается 0. В запросе два 5 делится на 3, целая часть будет 1 и возвращается остаток 2. В запросе номер три семь делится на 35. Так как делитель больше чем делимое – возвращается делимое, т.е. целая часть 0. Запрос четыре использует дробное число как делимое. Целой частью будет один и остаток будет 2.2.

Tip

Любое чётное число делится на два без остатка, любое нечётное число при делении на два вернёт остаток 1. Поэтому функцию MOD часто используют чтобы отличать чётные и нечётные числа.

Работа с датами

Функции работы с датами предлагают удобный способ решать задачи, связанные с датами без необходимости учитывать высокосные года, сколько дней в конкретном месяце. Вначале рассмотрим, как хранятся данные типа дата и форматирование даты, а также функцию SYSDATE. Затем рассмотрим функции ADD_MONTHS, MONTHS_BETWEEN, LAST_DAT, NEXT_DAY, ROUND и TRUNC.

Хранение даты в базе данных

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

Функция SYSDATE

Функция SYSDATE не использует входные параметры и возвращает текущее время и дату установленную на сервере БД. По умолчанию функция SYSDATE возвращает дату в формате DD-MON-RR и отображает дату на сервере. Если сервер установлен в другом часовом поясе чем машина клиента, то время и дата, возвращаемые SYSDATE могут отличаться от локальных значений на клиентсой машине. Можно выполнить такой запрос для отображения системной даты на сервере

select sysdate from dual

Арифметика над датами

Следуещее уравнение отображает важный принцип при работе с датами

Date1 – Date2 = Num1

Дата может вычитаться из другой даты. Разница между двуми датами понимается как количество дней между ними. Любое число, включая дробные, может быть добавлено или вычтено из даты. В этом контексте число представляет собой количество дней. Сумма или разница между число и датой – это всегда дата. Этот принцип подразумевает что сложение, умножение или деление двух дат невозможен.

Функция MONTHS_BETWEEN

Функция MONTHS_BETWEEN возвращает количество месяцев между двумя обязательными входными параметрами. Синтаксис функции MONTHS_BETWEEN(date1, date2). Функция рассчитывает разницу между date1 и date2. Если date1 меньше чем date2, то возвращается отрицательное число. Возвращаемое значение может состоять из целой части, отражающей количество месяцев между двумя датами, и дробной части, отражающей сколько дней и часов осталось (основываясь на месяце равном 31 дню) после вычета целого количества месяцев. Целое число вовзращается если день сравниваемых месяцев одинаковый или последний день соответствующего месяца.

Следующие примеры используют функию MONTHS_BETWEEN

Query 1: select months_between(sysdate, sysdate-31) from dual;

Query 2: select months_between(’29-mar-2008′, ’28-feb-2008′) from dual;

Query 3: select months_between(’29-mar-2008′, ’28-feb-2008′) * 31 from dual;

Преположим что текущая дата 16 Апреля 2009. Запрос один вернёт один как количество месяцев между 16 апреля 2009 и 16 марта 2009. Запрос два неявно конвертирует литералы в даты используя формат DD-MON-YYYY. Так как часть о времени опущена Oracle установит значение времени 00.00.00 для обеих дат. Фукнция вернёт значение примерно равное 1.03225806. Целая часть результата обозначает что между датами один месяц. Между 28 февраля и 28 марта ровно один месяц. Тогда дробная часть должна показывать ровно один день. Результат включает в себя часы минуты и секунды, но в нашем случае временная составляющая дат одинаковая. Умножение 0.03225806 на 31 вернёт 1, так как дробная часть, возвращаемая MONTHS_BETWEEN, рассчитывается, допуская что месяц равен ровно 31 дню. Поэтому запрос номер три вернёт значение 32.

Exam tip

Популярной ошибкой является допущение что возвращаемый тип данных функции зависит от типа функции (функции работы с датой должны возвращать дату, функции обработки строк – строку). Это верное только для численных функций. Символьные функции и функции работы с датами могут возвращать значение любого типа данных. Например, INSTR явлется символьной функцией, а MONTS_BETWEEN функцией работы с датой, но обе они возвращают результатом число. Также часто ошибочно рассуждают что разница между датами – это дата, когда фактически это число.

Функция ADD_MONTHS

Функция ADD_MONTHS возвращает дату, полученную путём добавления определённого количества месяцев к исходной дате. У этой функции два обязательных параметра и синтаксис ADD_MONTHS(start date, number of months). Значение параметра number of months может быть отрицательным, тогда исходное значение будет уменьшаться на это количество месяцев и дробным, но учитываться будет только целая часть. Следующие три запроса показывают использование функции ADD_MONTHS

Query 1: select add_months(’07-APR-2009′, 1) from dual;

Query 2: select add_months(’31-DEC-2008′, 2.5) from dual;

Query 3: select add_months(’07-APR-2009′, -12) from dual;

Результатом первого запроса буде 7 мая 2009, так как день остаётся одинаковым если это возможно и месяц увеличивается на один. Во втором запросе число месяцев дробное, что игнорируется, то есть этот запроса равен ADD_MONTHS(’31-DEC-2008’,2). Добавление двух месяцев должно вернуть 31-FEB-2009, но такой даты не существует, поэтому возвращается последний день месяца. В последнем примере используется отрицательное число для параметра кол-во месяцев и возвращается дата 07-APR-2008 что на двенадцать месяцев раньше, чем исходное значение.

Функция NEXT_DAY

Функция NEXT_DATE возвращает следующий ближайший заданный день недели после исходной даты. У этой функции два обязательных параметра и синтаксис NEXT_DAY(start date, day of the week). Функция выичсляет значение, когда заданный day of the week наступит после start date. Параметр day of the week может быть задан как числом, так и строкой. Допустимые значения определяются параметром NLS_DATE_LANGUAGE и по умолчанию используются три первые буквы названия дня недели в любом регистре (SUN, mon etc) или целые числа где 1 равно воскресенью, 2 – понедельник и так далее. Также имена дней недели могут быть более чем три символа; например, воскресенье можно указать как sun, sund, Sunday. Рассмотрим несколько запросов

Query 1: select next_day(’01-JAN-2009′, ‘tue’) from dual;

Query 2: select next_day(’01-JAN-2009′, ‘WEDNE’) from dual;

Query 3: select next_day(’01-JAN-2009′, 5) from dual;

1 января 2009 года это четверг. Следущий вторник будет через 5 дней, 6 января 2009 года. Второй запрос вернёт 7 января 2009 – следующая среда после 1 января. Третий запрос использует число как параметр и если у вас установлены Американские значения, то пятый день — это четверг. Следующий четверг после 1 января ровно через неделю – 8 января 2009 года.

Функция LAST_DAY

Функция LAST_DAY возвращает дату последнего дня месяца исходной даты. Эта функция требует один обязательные параметр и синтаксис LAST_DAY(start date). Функция выбирает месяц исходной даты и затем расчитывает последний день месяца. Следующий запрос вернёт 31 января 2009 года

select last_day(’01-JAN-2009′) from dual;

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

Функция ROUND округляет значение даты до заданной точности даты. Возвращаемое значение округляется либо к большему, либо r меньшему значению в зависимости от значения округляемого элемента. Эта функция требует один обязательный параметр и допускает один необязательные и синтаксис функции ROUND(source date, [date precision forma]). Параметром source data может быть любой элемент типа данных дата. Параметр date precision format определяет уровень округления и значение по умолчанию – день. Параметром date precision format может быть век (CC) год YYYY квартал Q месяц M неделя W день DD час HH минута MI.

Округления до века эквивалентно добавление единицы к текущему веку. Округление до месяца будет в большую сторону если день больше 16 иначе будет округление до первого дня месяца. Если месяц от одного до шести округление будет до начала текущего года, иначе вернётся дата начала следующего года. Рассмотрим запрос

Предположим, что этот запрос был выполнен 17 апреля 2009 года в 00:05. Вначале происходит округление текущей даты до дня (параметр точности явно неуказан). Так как время 00:05 то день не округляется в большую сторону.Так как 1 апреля 2009 года это среда, то второй столбец вернёт среду той недели, в которую входит исходная дата. Первая среда недели, в которую входит 19 апреля – это 15 апреля 2009 года. Третий столбец оругляет месяц до следующего (так как 17 больше 16) и возвращает 01 мая 2009. Поледний столбец округляет дату до года и возвращает 1 явнваря 2009 года, так как апрель это 4ый месяц.

Функция TRUNC при работе с датами

Функция TRUNC сокращает дату основываясь на параметре точности. У этой функции один параметр обязательный и один нет и синтаксис вызова TRUNC(source date, [date precision format]). Параметром source date может быть любая валидная дата. Параметр date precision format определяет уровень сокращения даты и необязателен, значение по умолчанию – сокращение до дня. Это значит что все значения времени обнуляются – 00 часов 00 минут 00 секунд. Сокращение до месяца вернёт дату равную первому дню месяца исходной даты. Сокращение до года – вернёт первый день года исходной даты. Рассмотрим запрос, использующий функцию с разными параметрами

Этот запрос выполнятся 17 апреля в 00:05. Первый столбец сокращает системную дату до дня, время преобразуется из 00:05 в 00:00 (параметр точности явно неуказан, используется значение по умолчанию) и возвращается текущий день. Второй столбец сокращает дату до такого же дня недели, который был первого числа месяца (среда) и возвращает среду текущей недели – 15 апреля. Третий столбец сокращает дату до месяца и возвращает первый день месяца – 1 апреля. Четвертый столбец сокращает дату до года и возвращает первый день года.

INSTR ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию INSTR с синтаксисом и примерами.

Описание

Oracle/PLSQL функция INSTR возвращает n-е вхождение подстроки в строке.

Синтаксис

Синтаксис функции Oracle/PLSQL INSTR:

INSTR( string, substring [, start_position [, nth_appearance ] ] )

Параметры или аргументы

string является строка для поиска. string может быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

substring подстрока для поиска в строке. substring может быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

start_position является положение символа в строке, с которого начнется поиск. Этот аргумент является необязательным. Если аргумент опущен, то по умолчанию 1. Первая позиция в строке 1. Если параметр start_position отрицательный, то функция INSTR рассчитывает позицию start_position в обратном направлении от конца строки, а затем ищет к началу строки.

nth_appearance является n-м вхождением подстроки. Аргумент не является обязательным. Если опущен, то по умолчанию 1.

Примечание

• Если подстрока не найдена в строке, то функция INSTR вернет 0.

Применение

Функцию INSTR можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Рассмотрим несколько примеров функции INSTR и изучим, как использовать функцию INSTR в Oracle/PLSQL.

SQL> SELECT INSTR(‘На дворе трава’, ‘а’) FROM DUAL;
—Результат: 2

SQL> SELECT INSTR(‘На дворе трава’, ‘а’, 1, 1) FROM DUAL;
—Результат: 2

SQL> SELECT INSTR(‘На дворе трава’, ‘а’, 1, 2) FROM DUAL;
—Результат: 12

SQL> SELECT INSTR(‘На дворе трава’, ‘а’, 1, 3) FROM DUAL;
—Результат: 14

SQL> SELECT INSTR(‘На дворе трава’, ‘а’, -3, 2) FROM DUAL;
—Результат: 2

Oracle / PLSQL: функция SUBSTR

В этом руководстве Oracle объясняется, как использовать функцию Oracle / PLSQL SUBSTR с синтаксисом и примерами.

Описание

Функции Oracle / PLSQL SUBSTR позволяют извлекать подстроку из строки.

Синтаксис

Синтаксис функции SUBSTR в Oracle / PLSQL:

 SUBSTR (строка; начальная_позиция [, длина]) 

Параметры или аргументы

строка

Исходная строка.

начало_положение

Исходное положение для добычи. Первая позиция в строке всегда 1.

длина

Необязательно. Это количество извлекаемых символов. Если этот параметр не указан, функция SUBSTR вернет всю строку.

Возвращает

Функция SUBSTR возвращает строковое значение.
Если длина — отрицательное число, тогда функция SUBSTR вернет значение NULL.

Примечание

• Если start_position равно 0, тогда функция SUBSTR обрабатывает start_position как 1 (то есть: первая позиция в строке).
• Если start_position — положительное число, тогда функция SUBSTR запускается с начала строки.
• Если start_position — отрицательное число, тогда функция SUBSTR начинается с конца строки и ведет отсчет в обратном порядке.
• См. Также функцию REGEXP_SUBSTR.

Относится к

Функцию SUBSTR можно использовать в следующих версиях Oracle / PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Давайте рассмотрим несколько примеров функции Oracle SUBSTR и рассмотрим, как использовать функцию SUBSTR в Oracle / PLSQL.

Например:

 SUBSTR ('Это тест'; 6, 2)
  Результат:  'is'

SUBSTR ('Это тест'; 6)
  Результат:  'это тест'

SUBSTR ('TechOnTheNet'; 1, 4)
  Результат:  «Технология».

SUBSTR ('TechOnTheNet'; -3; 3)
  Результат:  'Нетто'

SUBSTR ('TechOnTheNet'; -6; 3)
  Результат:  'The'

SUBSTR ('TechOnTheNet'; -8; 2)
  Результат:  'Вкл' 

.

Oracle REGEXP_SUBSTR

Функция Oracle REGEXP_SUBSTR () — это расширенная версия функции SUBSTR () , которая позволяет вам искать подстроки на основе регулярного выражения. Вместо того, чтобы возвращать позицию подстроки, он возвращает часть исходной строки, которая соответствует регулярному выражению.

Синтаксис

Ниже показан синтаксис функции Oracle REGEXP_SUBSTR () :

 
  REGEXP_SUBSTR (исходная_строка, шаблон
              [, начальная_позиция
                 [, вхождение
                    [, match_parameter
                       [, подвыражение
                       ]
                    ]
                 ]
              ]
             )
  
 

Аргументы

Функция Oracle REGEXP_SUBSTR () принимает 6 аргументов:

1) source_string

— это строка для поиска.

2) шаблон

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

3) start_position

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

Аргумент start_position является необязательным. Его значение по умолчанию — 1. Таким образом, если вы не укажете его явно, функция REGEXP_SUBSTR () начнет поиск с начала исходной строки.

4) вхождение

— положительное целое число, указывающее, какое вхождение шаблона поиска должна искать функция REGEXP_SUBSTR () .

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

5) match_parameter

— это буквальная строка, которая определяет поведение сопоставления по умолчанию для функции REGEXP_SUBSTR () .

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

• «i» указывает соответствие без учета регистра.
• «c» указывает соответствие с учетом регистра.
• «n» позволяет символу точки (.) Соответствовать символу новой строки. Если вы не укажете этот параметр явно, функция REGEXP_SUBSTR () не будет использовать точку для соответствия символу новой строки.
• ‘m’ рассматривает исходную строку как многострочную строку.

Поскольку аргумент match_parameter является необязательным, поэтому, если вы его опустите, функция REGEXP_SUBSTR () будет вести себя следующим образом:

• Сопоставление чувствительности к регистру определяется параметром NLS_SORT .
• Точка (.) Не соответствует символу новой строки.
• Исходная строка рассматривается как отдельная строка.

6) подвыражение

— положительное целое число, значение которого от 0 до 9 указывает, какое подвыражение в регулярном выражении является целевым.

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

Функция REGEXP_SUBSTR () возвращает часть исходной строки, которая соответствует регулярному выражению.

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

Примеры

Предположим, у нас есть следующая строка:

 
  'This is a regexp_substr demo'
  
 

Если вы хотите получить четвертое слово в приведенной выше строке, используйте функцию REGEXP_SUBSTR () следующим образом:

 
  SELECT
  regexp_substr ('Это демонстрация regexp_substr', '[[: alpha:]] +', 1, 4
  ) the_4th_word
ОТ
  двойной;
  
 

Результат:

 
  regexp_substr
  
 

В этом примере:

• '[[: alpha:]] +' — это шаблон регулярного выражения, который соответствует любому слову.
• 1 указывает функции начать поиск начала строки.
• 4 указывает четвертое вхождение.

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

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

 
  SELECT
  regexp_substr ('Это демонстрация regexp_substr', '[[: alpha:]] +', 1, LEVEL) regexp_substr
ОТ
  двойной
 СОЕДИНЕНИЕ ПО УРОВНЮ <= regexp_count ('Это демонстрация regexp_substr', '') + 1;
  
 

Обратите внимание, что общее количество совпадений рассчитывается путем подсчета количества пробелов в исходной строке плюс 1 с помощью функции REGEXP_COUNT () .

См. Следующую таблицу продуктов в базе данных примера:

Следующий оператор выбирает все материнские платы из таблицы продуктов :

 
  SELECT
  идантификационный номер продукта,
  наименование товара,
  описание
ОТ
  продукты
ГДЕ
  category_id = 4
СОРТИРОВАТЬ ПО
  наименование товара ;  
 

Предположим, вы хотите получить максимальный объем ОЗУ, который может поддерживать материнская плата, вы можете извлечь эту информацию из столбца описания, используя следующее регулярное выражение:

 
  '\ d + (GB | TB)'
  
 

Это регулярное выражение означает соответствие одному или нескольким числам (\ d +), за которыми следует строка в ГБ или ТБ (ГБ | ТБ).

Следующий запрос иллюстрирует идею:

 
  SELECT
  идантификационный номер продукта,
  наименование товара,
  описание,
  REGEXP_SUBSTR (описание, '\ d + (ГБ | ТБ)') max_ram
ОТ
  продукты
ГДЕ
  category_id = 4;
  
 

И вот результат:

В этом руководстве вы узнали, как использовать Oracle REGEXP_SUBSTR () для извлечения части строки на основе регулярного выражения.

• Было ли это руководство полезным?
• Да Нет

.

Oracle / PLSQL: функция REGEXP_SUBSTR

В этом руководстве Oracle объясняется, как использовать функцию Oracle / PLSQL REGEXP_SUBSTR с синтаксисом и примерами.

Описание

Функция Oracle / PLSQL REGEXP_SUBSTR является расширением функции SUBSTR. Эта функция, представленная в Oracle 10g, позволяет извлекать подстроку из строки, используя сопоставление с шаблоном регулярного выражения.

Синтаксис

Синтаксис функции REGEXP_SUBSTR в Oracle:

 REGEXP_SUBSTR (строка, шаблон [, начальная_позиция [, nth_appearance [, match_parameter [, sub_expression]]]]) 

Параметры или аргументы

строка

Строка для поиска. Соответствует началу строки. Если используется с match_parameter из 'm', он соответствует началу строки в любом месте в пределах выражения . $ Соответствует концу строки. Если используется с match_parameter из 'm', он соответствует концу строки в любом месте в пределах выражения . * Соответствует нулю или более вхождений. + Соответствует одному или нескольким вхождениям.] Используется для указания несоответствующего списка, в котором вы пытаетесь сопоставить любой символ, кроме тех, что в списке. () Используется для группировки выражений как части выражения. {м} Совпадений m раз. {м,} Совпадает не менее m раз. {m, n} Соответствует не менее m раз, но не более n раз. \ п n - число от 1 до 9. Соответствует n-му подвыражению, найденному в () до того, как встретится \ n. [..] Соответствует одному элементу сопоставления, который может содержать более одного символа. [::] Соответствует классам символов. [==] Соответствует классам эквивалентности. \ d Соответствует цифровому символу. \ D Соответствует нецифровому символу. \ w Соответствует словесному символу. \ Вт Соответствует несловесному символу. \ с Соответствует пробельному символу. \ S соответствует непробельному символу. \ A Соответствует началу строки или соответствует концу строки перед символом новой строки. \ Z Соответствует концу строки. *? Соответствует предыдущему шаблону, равному нулю или более. +? Одно или несколько вхождений предыдущего шаблона. ?? Соответствует предыдущему шаблону, равному нулю или одному вхождению. {n}? Соответствует предыдущему шаблону n раз. {n,}? Соответствует предыдущему шаблону не менее n раз. {n, m}? Соответствует предыдущему шаблону не менее n раз, но не более m раз.

начало_положение

Необязательно. Это позиция в строке , с которой начинается поиск. Если он опущен, по умолчанию используется 1, которая является первой позицией в строке.

nth_appearance

Необязательно.Это n-е появление образца в строке . Если опущено, по умолчанию используется 1, что является первым появлением шаблона в строке .

match_parameter

Необязательно. Это позволяет вам изменять поведение сопоставления для функции REGEXP_SUBSTR. Это может быть комбинация следующих элементов:

Значение	Описание
'c'	Выполнить сопоставление с учетом регистра.- начало строки, а $ - конец строки, независимо от положения этих символов в выражении . По умолчанию предполагается, что выражение представляет собой одну строку.
'x'	Пробельные символы игнорируются. По умолчанию пробельные символы сопоставляются, как и любые другие символы.

подвыражение

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

Возвращает

Функция REGEXP_SUBSTR возвращает строковое значение.
Если функция REGEXP_SUBSTR не находит ни одного вхождения шаблона , она вернет NULL.

Примечание

• Если есть конфликтующие значения, предоставленные для match_parameter , функция REGEXP_SUBSTR будет использовать последнее значение.
• Если вы опустите параметр match_behavior , функция REGEXP_SUBSTR будет использовать параметр NLS_SORT, чтобы определить, следует ли использовать поиск с учетом регистра, она будет считать, что строка является одной строкой, и предположить, что символ точки соответствует любому символу ( не символ новой строки).
• См. Также функцию SUBSTR.

Относится к

Функцию REGEXP_SUBSTR можно использовать в следующих версиях Oracle / PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g

Пример - совпадение слов

Начнем с извлечения первого слова из строки.

Например:

 SELECT REGEXP_SUBSTR ('TechOnTheNet - отличный ресурс', '(\ S *) (\ s)')
ОТ двойного;

  Результат:  'TechOnTheNet' 

В этом примере будет возвращено «TechOnTheNet», потому что он извлечет все непробельные символы, как указано в (\ S *) , а затем первый пробельный символ, как указано в (\ s) . Результат будет включать как первое слово, так и пробел после слова.

Если вы не хотите включать пробел в результат, мы могли бы изменить наш пример следующим образом:

 SELECT REGEXP_SUBSTR ('TechOnTheNet - отличный ресурс', '(\ S *)')
ОТ двойного;

  Результат:  'TechOnTheNet' 

В этом примере будет возвращено «TechOnTheNet» без пробела в конце.

Если бы мы хотели найти второе слово в строке, мы могли бы изменить нашу функцию следующим образом:

 SELECT REGEXP_SUBSTR ('TechOnTheNet - отличный ресурс', '(\ S *) (\ s)', 1, 2)
ОТ двойного;

  Результат:  'равно' 

В этом примере будет возвращено «есть» с пробелом в конце строки.

Если бы мы хотели найти третье слово в строке, мы могли бы изменить нашу функцию следующим образом:

 SELECT REGEXP_SUBSTR ('TechOnTheNet - отличный ресурс', '(\ S *) (\ s)', 1, 3)
ОТ двойного;

  Результат:  'a' 

В этом примере возвращается 'a' с пробелом в конце строки.

Пример - совпадение цифр

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

Например:

 SELECT REGEXP_SUBSTR ('2, 5 и 10 - числа в этом примере', '\ d')
ОТ двойного;

  Результат:  2 

В этом примере будет извлечена первая числовая цифра из строки, как указано \ d . В этом случае он будет соответствовать номеру 2.

Мы можем изменить наш шаблон на поиск двузначного числа.

Например:

 SELECT REGEXP_SUBSTR ('2, 5 и 10 - числа в этом примере', '(\ d) (\ d)')
ОТ двойного;

  Результат:  10 

В этом примере будет извлечено число, в котором две цифры расположены рядом, как указано в (\ d) (\ d) . В этом случае он пропустит числовые значения 2 и 5 и вернет 10.

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

Например:

 ВЫБРАТЬ REGEXP_SUBSTR (адрес, '(\ d) (\ d)')
ОТ контактов; 

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

Пример - соответствие более чем одной альтернативе

В следующем примере, который мы рассмотрим, используется | выкройка. Модель | Шаблон используется как «ИЛИ» для указания более чем одной альтернативы.

Например:

 ВЫБРАТЬ REGEXP_SUBSTR ('Андерсон', 'a | e | i | o | u')
ОТ двойного;

  Результат:  'e' 

Этот пример вернет 'e', ​​потому что он ищет первую гласную (a, e, i, o или u) в строке.Поскольку мы не указали значение match_parameter , функция REGEXP_SUBSTR будет выполнять поиск с учетом регистра, что означает, что «A» в «Anderson» не будет сопоставлено.

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

 ВЫБРАТЬ REGEXP_SUBSTR ('Андерсон', 'a | e | i | o | u', 1, 1, 'i')
ОТ двойного;

  Результат:  'A' 

Теперь, поскольку мы предоставили match_parameter из «i», запрос вернет в качестве результата «A».На этот раз «А» в «Андерсоне» будет совпадать.

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

Допустим, у нас есть таблица контактов со следующими данными:

Теперь давайте запустим следующий запрос:

 SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a | e | i | o | u', 1, 1, 'i') КАК «Первая гласная»
ОТ контактов; 

Это результаты, которые будут возвращены запросом:

Пример - совпадение по nth_occurrence

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

Первое появление

Давайте посмотрим, как извлечь первое вхождение шаблона в строку .

Например:

 ВЫБРАТЬ REGEXP_SUBSTR ('TechOnTheNet', 'a | e | i | o | u', 1, 1, 'i')
ОТ двойного;

  Результат:  'e' 

Этот пример вернет 'e', ​​потому что он извлекает первое вхождение гласной (a, e, i, o или u) в строке.

Второе появление

Затем мы извлечем для второго вхождения шаблона в строку .

Например:

 ВЫБРАТЬ REGEXP_SUBSTR ('TechOnTheNet', 'a | e | i | o | u', 1, 2, 'i')
ОТ двойного;

  Результат:  'O' 

Этот пример вернет «O», потому что он извлекает второе вхождение гласной (a, e, i, o или u) в строку.

Третье появление

Например:

 ВЫБРАТЬ REGEXP_SUBSTR ('TechOnTheNet', 'a | e | i | o | u', 1, 3, 'i')
ОТ двойного;

  Результат:  'e' 

Этот пример вернет 'e', ​​потому что он извлекает третье вхождение гласной (a, e, i, o или u) в строке.

.

SUBSTR - получить подстроку из строки - миграция Oracle на SQL Server

В Oracle функция SUBSTR возвращает подстроку из строки, начинающейся с указанной позиции и имеющей указанную длину.
(или до конца строки по умолчанию).

В SQL Server вы можете использовать функцию SUBSTRING, но она не позволяет вам указывать отрицательную начальную позицию, а подстроку
длина должна быть указана.

Оракул :

 - получить первые 3 символа
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', 1, 3) ИЗ двойного;
  # Новый

  - Получить последние 4 символа (отрицательная начальная позиция)
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', -4) ИЗ двойного;
  # York 

SQL Server :

 - получить первые 3 символа
  ВЫБЕРИТЕ ПОДСТРОКУ («Нью-Йорк», 1, 3);
  # Новый

  - Получить последние 4 символа
  ВЫБРАТЬ ВПРАВО («Нью-Йорк», 4);
  # York 

Сводная информация:

Последнее обновление : Oracle 11g R2 и Microsoft SQL Server 2012

Если функция Oracle SUBSTR используется с положительной начальной позицией и указана длина, вы можете преобразовать ее в SUBSTRING в SQL Server:

Оракул :

 - Получить 4 символа, начиная с позиции 5
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', 5, 4) ИЗ двойного;
  # York 

SQL Server :

 - Получить 4 символа, начиная с позиции 5
  ВЫБЕРИТЕ ПОДСТРОКУ («Нью-Йорк», 5, 4);
  # York 

Длина по умолчанию

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

Оракул :

 - Получить подстроку с позиции 5 до конца строки
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', 5) ИЗ двойного;
  # York 

SQL Server :

 DECLARE @str VARCHAR (10) = «Нью-Йорк»;

  - Получить подстроку с позиции 5 до конца строки
  ВЫБРАТЬ ПОДСТРОКУ (@str, 5, LEN (@str));
  # York 

Кроме того, вы можете использовать функцию RIGHT для преобразования Oracle SUBSTR, используемого без длины:

SQL Server :

 DECLARE @str VARCHAR (10) = «Нью-Йорк»;

  - Получить подстроку с позиции 5 до конца строки
  ВЫБРАТЬ ВПРАВО (@str, LEN (@str) + 1-5);
  # York 

Отрицательное начальное положение

В Oracle, если начальная позиция отрицательна, функция SUBSTR вычисляет ее от конца строки.В SQL Server вы можете
используйте функции SUBSTRING и LEN:

Оракул :

 - Получить 3 символа из позиции 4, считая от конца строки
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', -4, 3) ИЗ двойного;
  # Йор 

SQL Server :

 DECLARE @str VARCHAR (10) = «Нью-Йорк»;

  - Получить 3 символа из позиции 4, считая от конца строки
   ВЫБРАТЬ ПОДСТРОКУ (@str, LEN (@str) + 1-4, 3);
  # Йор 

Если указана отрицательная начальная позиция, но длина опущена, вы также можете использовать функцию RIGHT в SQL Server:

Оракул :

 - Получить последние 4 символа в строке
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', -4) ИЗ двойного;
  # York 

SQL Server :

 - Получить последние 4 символа в строке
  ВЫБРАТЬ ВПРАВО («Нью-Йорк», 4);
  # York 

Вы также можете использовать функцию ВПРАВО, если абсолютные значения отрицательной начальной позиции и длины равны:

Оракул :

 - Получить последние 4 символа в строке
  ВЫБЕРИТЕ SUBSTR ('Нью-Йорк', -4, 4) ИЗ двойного;
  # York 

SQL Server :

 - Получить последние 4 символа в строке
  ВЫБРАТЬ ВПРАВО («Нью-Йорк», 4);
  # York 

Oracle 11g R2 Справочник по языку SQL

Microsoft SQL Server 2012 - Электронная документация

SQLines предлагает услуги по миграции баз данных и приложений Oracle на Microsoft SQL Server.Для получения более подробной информации, пожалуйста, свяжитесь с нами.

.