Oracle выделить цифры из строки
Принимая во внимание другие ответы, я придумал свой: Попробуйте это:
NB: работает только для первого int в строке, например: abc123vfg34 возвращает 123.
С предыдущими запросами я получаю эти результаты:
'AAAA1234BBBB3333' >>>> Выход: 1234
'-çã + 0! \ aº1234' >>>> Вывод: 0
Код ниже возвращает все числовые символы:
1-й выход: 12343333
2-й выход: 01234
Я обнаружил, что этот подход работает примерно в 3 раза быстрее, чем ответ, получивший наибольшее количество голосов. Создайте следующую функцию dbo.GetNumbers:
Тестирование
Тестирование с целью:
Тестирование производительности: начните с настройки тестовых данных .
Теперь тестируем функцию dbo.GetNumbers:
Затем тестирование UDF сверху проголосовал за ответ на тех же данных.
Десятичные дроби
Если вам нужно, чтобы он обрабатывал десятичные дроби, вы можете использовать любой из следующих подходов, я не обнаружил заметных различий в производительности между ними.
- менять '4' к '[0-9.]'
- менять Character LIKE '8' к ISNUMERIC(Character) = 1 (SQL обрабатывает одно десятичное число как числовое)
Бонус
Вы можете легко адаптировать его к различным требованиям, заменив WHERE Character LIKE '8' со следующими параметрами:
- WHERE Letter LIKE '[a-zA-Z]' --Get only letters
- WHERE Letter LIKE '[0-9a-zA-Z]' --Remove non-alphanumeric
- WHERE Letter LIKE '[^0-9a-zA-Z]' --Get only non-alphanumeric
Сначала выясните начальную длину числа, затем переверните строку, чтобы снова узнать первую позицию (что даст вам конечную позицию числа с конца). Теперь, если вы вычтете 1 из обоих чисел и вычтете его из всей длины строки, вы получите только длину числа. Теперь получите номер с помощью SUBSTRING
Просто небольшая модификация ответа @Epsicron
нет необходимости во временной переменной
Этот UDF будет работать для всех типов строк:
У меня не было прав на создание функций, но был текст, как
И нужно было извлечь цифры из середины
Обратите внимание, что предполагается, что числа сгруппированы, а не в начале и в конце строки.
Хотя это старая ветка, она первая в поиске Google, но я пришел к другому ответу, чем то, что было раньше. Это позволит вам передать свои критерии того, что должно оставаться в строке, какими бы они ни были. Вы можете поместить его в функцию, которая будет вызывать снова и снова, если хотите.
Функция T-SQL для чтения всех целых чисел из текста и возврата одного по указанному индексу, начиная слева или справа, также используя начальный термин поиска (необязательно):
Это решение отличается от всех предыдущих, а именно:
- Нет необходимости создавать функцию
- Нет необходимости использовать сопоставление с образцом
- Нет необходимости во временной таблице
- В этом решении используется рекурсивное общее табличное выражение (CTE)
Но сначала обратите внимание, что вопрос не указывает, где хранятся такие строки. В моем решении ниже я создаю CTE как быстрый и грязный способ поместить эти строки в какую-то "исходную таблицу".
Также обратите внимание - в этом решении используется рекурсивное общее табличное выражение (CTE), поэтому не запутайтесь, используя здесь два CTE. Первый - просто сделать данные доступными для решения - но для решения этой проблемы требуется только второй CTE. Вы можете адаптировать код, чтобы этот второй CTE запросил вашу существующую таблицу, представление и т. Д.
И, наконец, мое кодирование является подробным, я пытаюсь использовать имена столбцов и CTE, которые объясняют, что происходит, и вы можете немного упростить это решение. Я добавил несколько псевдотелефонных номеров с некоторым (ожидаемым и нетипичным, в зависимости от обстоятельств) форматированием для удовольствия.
Так что здесь происходит?
В основном в нашем CTE мы выбираем первый символ и используем try_cast ( см. документы), чтобы передать его в tinyint (который является достаточно большим типом данных для однозначного числа). Обратите внимание, что правила преобразования типов в SQL Server говорят, что пустая строка (или пробел, если на то пошло) будет преобразована в ноль, поэтому nullif добавляется, чтобы заставить пробелы и пустые строки преобразоваться в null ( см. обсуждение) (иначе наш результат будет включать нулевой символ каждый раз, когда в исходных данных встречается пробел).
CTE также возвращает все, что находится после первого символа, и это становится входом для нашего рекурсивного вызова CTE; другими словами: теперь давайте обработаем следующий символ.
Наконец, поле newString в CTE генерируется (во втором SELECT ) через конкатенацию. С рекурсивными CTE тип данных должен совпадать между двумя SELECT операторы для любого заданного столбца, включая размер столбца. Поскольку мы знаем, что добавляем (максимум) один символ, мы приводим этот символ к nvarchar(1) и приводим newString (пока) как nvarchar(3999). В результате получается nvarchar(4000), что соответствует приведению типов, которое мы выполняем в первом SELECT .
Если вы запустите этот запрос и исключите WHERE Вы получите представление о том, что происходит, но строки могут быть в странном порядке. (Вы не обязательно увидите сгруппированные вместе все строки, относящиеся к одному входному значению, но вы все равно сможете следить за ним).
Надеюсь, это интересный вариант, который может помочь некоторым людям, которым требуется решение, строго основанное на выражениях.
Функция 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
Шаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:
| Значение | Описание |
|---|---|
| ^ | Соответствует началу строки. При использовании match_parameter с m , соответствует началу строки в любом месте в пределах выражения. |
| $ | Соответствует концу строки. При использовании match_parameter с m , соответствует концу строки в любом месте в пределах выражения. |
| * | Соответствует нолю или более вхождений. |
| + | Соответствует одному или более вхождений. |
| ? | Соответствует нолю или одному вхождению. |
| . | Соответствует любому символу, кроме NULL. |
| | | Используется как "OR", чтобы указать более одной альтернативы. |
| [ ] | Используется для указания списка совпадений, где вы пытаетесь соответствовать любому из символов в списке. |
| [^ ] | Используется для указания списка nonmatching, где вы пытаетесь соответствовать любому символу, за исключением тех кто в списке. |
| ( ) | Используется для групповых выражений в качестве подвыражений. |
| Соответствует m раз. | |
| Соответствие как минимум m раз. | |
| Соответствие как минимум m раз, но не более n раз. | |
| \n | n представляет собой число от 1 до 9. Соответствует n -му подвыражению находящемуся в ( ) перед \n. |
| [..] | Соответствует одному сопоставлению элемента, который может быть более одного символа. |
| [::] | Соответствует классу символов. |
| [==] | Соответствует классу эквивалентности |
| \d | Соответствует цифровому символу. |
| \D | Соответствует не цифровому символу. |
| \w | Соответствует текстовому символу. |
| \W | Соответствует не текстовому символу. |
| \s | Соответствует символу пробел. |
| \S | Соответствует не символу пробел. |
| \A | Соответствует началу строки или соответствует концу строки перед символом новой строки. |
| \Z | Соответствует концу строки. |
| *? | Соответствует предыдущему шаблону ноль или более вхождений. |
| +? | Соответствует предыдущему шаблону один или более вхождений. |
| ?? | Соответствует предыдущему шаблону ноль или одному вхождению. |
| ? | Соответствует предыдущему шаблону n раз. |
| ? | Соответствует предыдущему шаблону, по меньшей мере n раз. |
| ? | Соответствует предыдущему шаблону, по меньшей мере n раз, но не более m раз. |
start_position
Необязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.
nth_appearance
Необязательный. Это n -й вид шаблона в строке. Если этот параметр опущен, по умолчанию он равен 1, который является первым вхождением шаблона в строке. Если вы укажете 0 для этого параметра, все вхождения шаблона в строке будут заменены.
match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_REPLACE. Это может быть комбинацией следующих значений:
| Значение | Описание |
|---|---|
| 'c' | Выполняет чувствительное к регистру согласование. |
| 'i' | Выполняет не чувствительное к регистру согласование. |
| 'n' | Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы. |
| 'm' | Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке. |
| 'x' | Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ. |
subexpression
Необязательный. Используется, когда шаблон имеет подвыражения, и вы хотите указать, какое подвыражение в шаблоне является целью. Это целочисленное значение от 0 до 9, указывающее, что подвыражение соответствует шаблону.
- Функция REGEXP_SUBSTR возвращает строковое значение.
- Если функция REGEXP_SUBSTR не обнаруживает какого-либо вхождения шаблона, она возвращает NULL.
Примечание
- Если для параметра match_parameter имеются конфликтующие значения, функция REGEXP_SUBSTR будет использовать последнее значение.
- См. Также функцию SUBSTR.
Применение
Функция REGEXP_SUBSTR может использоваться в следующих версиях Oracle / PLSQL:
У меня есть таблица с двумя столбцами: price (int) и price_display (varchar).
цена-это фактическая числовая цена, например "9990"
price_display-это визуальное представление, например "$9.99"или" 9.99 Fr"
Я смог подтвердить совпадение двух столбцов с помощью regexp:
price_display не обработать формат(цена/1000, 2)
но в случае несоответствия я хочу извлечь значение из столбец price_display и установите его в столбец price, все в контексте инструкции update. Я не смог понять как.
эта функция выполняет работу только возврата цифр 0-9 из строки, что делает работу хорошо, чтобы решить вашу проблему, независимо от того, какие префиксы или исправления у вас есть.
скопировано здесь для справки:
один подход был бы использовать заменить() функция:
это работает для данных примеров, которые вы дали:
оба результата в 999 в моем тесте. При таком обновлении важно сначала создать резервную копию базы данных и знать форматы элементов. Вы можете увидеть всех "злодеев", выполнив этот запрос:
для меня кастинг на поле сделал трюк:
CAST( price AS UNSIGNED ) / / для положительного целого числа
CAST( price AS SIGNED ) / / для отрицательного и положительного целых чисел
IF(CAST(price AS UNSIGNED)=0,REVERSE(CAST(REVERSE(price) AS UNSIGNED)),CAST(price AS UNSIGNED)) / / Fix когда цена начинается с чего-то еще, то цифра
Я создаю процедуру, которая обнаруживает первое число в строке и возвращает это, если не возвращает 0.
вернуть последнее число из строки:
после вызова этой функции:
выберите getLastNumber ("ssss111www222w"); печать 222
выберите getLastNumber ("ssss111www222www3332"); печать 3332
это "кодирование ужасов", схемы реляционных баз данных должны не пишется так!
вам нужно написать сложный и ненужный код для проверки данных.
попробуйте что-то вроде этого:
кроме того, вы можете использовать float , double или real вместо целого.
Если вам нужно хранить данные валюты, вы можете добавить поле валюты или использовать функции языкового стандарта систем для его отображения в правильном формате.
Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.
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.
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
| ID | Актриса | Мужья |
|---|---|---|
| 1 | Анджелина Джоли | Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт |
| 2 | Шарлиз Терон | |
| 3 | Пенелопа Крус | Хавьер Бардем |
Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.
Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.
Ну и Пенелопа Крус — замужем всего один раз. Какая скука.
Но это все прелюдия, а на деле нужно получить следующий результат
| Актриса | Муж | Номер мужа п/п |
|---|---|---|
| Анджелина Джоли | Джонни Ли Миллер | 1 |
| Анджелина Джоли | Билли Боб Торнтон | 2 |
| Анджелина Джоли | Брэд Питт | 3 |
| Шарлиз Терон | ||
| Пенелопа Крус | Хавьер Бардем | 1 |
По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.
Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.
- Генерируются новые строки с помощью connect by level.
- Через regexp_count вычисляется количество чисел в строке между разделителями – это количество определяет верхнюю границу для генератора строк.
- С помощью regexp_substr извлекаются числа из строки. Номер вхождения шаблона в строку (4-й аргумент регулярки) соответствует значению псевдостолбца rownum — номер п/п сгенерированной строки. Вместо rownum можно было использовать и level, результат был бы аналогичным.
В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.
Объединив подходы, получаем следующее.
Спецэффект № 1.
Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.
Спецэффект № 2.
Это было решение через коллекции.
UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ.
Читайте также: