Oracle удалить из строки все кроме цифр
Функция Oracle/PLSQL REGEXP_REPLACE является расширением функции REPLACE. Эта функция, введенная в Oracle 10g, позволит вам заменить последовательность символов в строке другим набором символов, используя сопоставление шаблонов регулярных выражений.
Синтаксис
Синтаксис функции Oracle/PLSQL REGEXP_REPLACE :
REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )Параметры или аргументы
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 раз. |
replacement_string
Необязательный. Соответствующие шаблоны в строке будут заменены на replace_string . Если параметр replacement_string опущен, то функция просто удаляет все совпадающие шаблоны и возвращает полученную строку.
start_position
Необязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.
nth_appearance
Необязательный. Это n -й вид шаблона в строке. Если этот параметр опущен, по умолчанию он равен 1, который является первым вхождением шаблона в строке. Если вы укажете 0 для этого параметра, все вхождения шаблона в строке будут заменены.
match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_REPLACE. Это может быть комбинацией следующих значений:
| Значение | Описание |
|---|---|
| 'c' | Выполняет чувствительное к регистру согласование. |
| 'i' | Выполняет не чувствительное к регистру согласование. |
| 'n' | Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы. |
| 'm' | Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке. |
| 'x' | Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ. |
Функция REGEXP_REPLACE возвращает строковое значение.
Примечание
- Если для параметра match_parameter имеются противоречивые значения, функция REGEXP_REPLACE будет использовать последнее значение.
- См. также функцию REPLACE.
Применение
Функция REGEXP_REPLACE может использоваться в следующих версиях Oracle/PLSQL:
Пример совпадения с первым словом
Рассмотрим пример использования функции REGEXP_REPLACE для замены первого слова в строке.
Просьба подсказать как не-хардкорным способом, а красивым и элегантным можно, к примеру, с помощью функции replace() удалить все символы из строки, которые не являются цифрами.
Не-хардкорным способом это к примеру не куча вложенных функций replace(replace(replace(etc.))) в друг друга.
Всем заранее спасибо!
__________________Помощь в написании контрольных, курсовых и дипломных работ здесь
Отбросить в строках БД все, кроме цифр
доброго дня! в строках бд нужно отбросить все, кроме цифр. Задаю запрос, возвращает не то. select.
Удалить все записи, кроме десяти последних
Можно определить количество записей в таблице и затем подсчитать, сколько записей надо удалить.
Удалить из поля ТЕЛЕФОН все кроме чисел
Есть таблица которая заново создается из запроса. В этой таблице находятся 3 поля с телефонами.
Удалить все записи, кроме 10 последних каждого users
Как удалить все посты пользователя,кроме 10 последних? DELETE from posts WHERE id NOT IN (SELECT.
Ребята всем спасибо за ответ, а нельзя ли это как-то сделать регуляркой и в операторе select?
а нельзя ли это как-то сделать регуляркой и в операторе select? Можно, если найдете готовую или напишите сами SQLCLR-библиотеку для работы с регулярными выражениями.
Удалить из таблицы все записи кроме записей по двум условиям
Здравствуйте! Подскажите как удалить из таблицы все записи кроме записей по двум условиям. Пробую.
Удалить из строки все символы кроме цифр
Программа просит ввести строку S и удаляет из неё все символы кроме цифр. Добавлено через 20.
Удалить из однотипных строчек все буквы и символы кроме цифр
есть переменная в которой храниться значение вот такого типа bet.html?hl=15157999. мне нужно.
Удалить через регулярку все символы кроме запятой и цифр
Что за регулярное выражение которое удалит из строчки все символы кроме запятой и цифр? И сразу по.
Вводится строка, в ней нужно удалить все символы кроме цифр
Вводится строка, в ней нужно удалить все символы кроме цифр
Недавно я смог избавиться от нерешенной внешней ошибки в Visual Studio 2012, просто перекомпилировав нарушивший файл. Когда я перестроил, ошибка исчезла.
Это обычно происходит, когда две (или более) библиотеки имеют циклическую зависимость. Библиотека A пытается использовать символы в B.lib и библиотеке B пытается использовать символы из A.lib. Ничего не существует для начала. Когда вы пытаетесь скомпилировать A, шаг ссылки завершится неудачно, потому что он не может найти B.lib. A.lib будет сгенерирован, но не будет dll. Затем вы компилируете B, который будет успешным и сгенерирует B.lib. Повторная компиляция A теперь будет работать, потому что теперь найден B.lib.
Это хорошо работает для меня:
Затем вызовите функцию так, чтобы увидеть оригинальное что-то рядом с дезинфицированным чем-то:
Вот элегантное решение, если ваш сервер поддерживает функцию TRANSLATE (на сервере sql он доступен на сервере sql 2017+, а также sql azure).
Во-первых, он заменяет любые нецифровые символы на @ персонаж. Затем он удаляет все буквы @. Возможно, вам придется добавить дополнительные символы, которые, как вы знаете, могут присутствовать во втором параметре вызова TRANSLATE.
В случае, если между цифрами могут быть некоторые символы (например, разделители тысяч), вы можете попробовать следующее:
Для обновления, добавьте ColToUpdate , чтобы выбрать список data cte:
Это сработало для меня:
- Я удалил одинарные кавычки.
- Затем я использовал замену "," на "." .
Конечно, это поможет кому-то:
Этот код работает, удаляя все цифры (то есть символы, которые нам нужны) из заданных строк заменяя их пробелами. Затем он проходит через исходную строку (включая цифры), удаляя все оставшиеся символы (то есть нечисловые символы), оставляя только цифры.
Причина, по которой мы делаем это в 2 шаги, а не просто удаление всех нечисловых символов, в первую очередь, есть только 10 цифр, в то время как существует огромное количество возможных символов; поэтому замена этого небольшого списка происходит относительно быстро; затем дает нам список тех нечисловых символов, которые фактически существуют в строке, поэтому мы можем заменить этот небольшой набор.
В методе используется рекурсивный SQL, используя общие табличные выражения (CTE).
В Oracle10g в области работы со строками произошли очень серьезные изменения: была реализована поддержка регулярных выражений. Причем речь идет не об упрощенной поддержке регулярных выражений вроде предиката LIKE, которая встречается в других СУБД. Компания Oracle предоставила в наше распоряжение отлично проработанный, мощный набор функций — то, что было необходимо в PL/SQL.
Проверка наличия совпадения
Регулярные выражения используются для описания текста, который требуется найти в строке (и возможно, подвергнуть дополнительной обработке). Давайте вернемся к примеру, который приводился ранее в этом блоге:
Допустим, мы хотим определить на программном уровне, содержит ли строка список имен, разделенных запятыми. Для этого мы воспользуемся функцией REGEXP_LIKE , обнаруживающей совпадения шаблона в строке:
Чтобы разобраться в происходящем, необходимо начать с выражения, описывающего искомый текст. Общий синтаксис функции REGEXP_LIKE выглядит так:
Здесь исходная_строка — символьная строка, в которой ищутся совпадения; шаблон — регулярное выражение, совпадения которого ищутся в исходной_строке; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Если функция REGEXP_LIKE находит совпадение шаблона в исходной_строке , она возвращает логическое значение TRUE ; в противном случае возвращается FALSE .
Процесс построения регулярного выражения выглядел примерно так:
- [a-z A-Z] Каждый элемент списка имен может состоять только из букв и пробелов. Квадратные скобки определяют набор символов, которые могут входить в совпадение. Диапазон a–z описывает все буквы нижнего регистра, а диапазон A–Z — все буквы верхнего регистра. Пробел находится между двумя компонентами выражения. Таким образом, этот шаблон описывает один любой символ нижнего или верхнего регистра или пробел.
- [a-z A-Z] * Звездочка является квантификатором — служебным символом, который указывает, что каждый элемент списка содержит ноль или более повторений совпадения, описанного шаблоном в квадратных скобках.
- [a-z A-Z] *, Каждый элемент списка должен завершаться запятой. Последний элемент является исключением, но пока мы не будем обращать внимания на эту подробность.
- ([a-z A-Z] *,) Круглые скобки определяют подвыражение, которое описывает некоторое количество символов, завершаемых запятой. Мы определяем это подвыражение, потому что оно должно повторяться при поиске.
- ([a-z A-Z ]*,)+ Знак + — еще один квантификатор, применяемый к предшествующему элементу (то есть к подвыражению в круглых скобках). В отличие от * знак + означает «одно или более повторений». Список, разделенный запятыми, состоит из одного или нескольких повторений подвыражения.
- ( [a-z A-Z]*,)+([a-z A-Z]*) В шаблон добавляется еще одно подвыражение: ( [a-z A-Z] *). Оно почти совпадает с первым, но не содержит запятой. Последний элемент списка не завершается запятой.
- ([a-z A-Z]*,)+([a-z A-Z]*) Мы добавляем квантификатор , чтобы разрешить вхождение ровно одного элемента списка без завершающей запятой.
- ^ ([a-z A-Z]*,)+([a-z A-Z]*)$ Наконец, метасимволы ^ и $ привязывают потенциальное совпадение к началу и концу целевой строки. Это означает, что совпадением шаблона может быть только вся строка вместо некоторого подмножества ее символов.
Функция REGEXP_LIKE анализирует список имен и проверяет, соответствует ли он шаблону. Эта функция оптимизирована для простого обнаружения совпадения шаблона в строке, но другие функции способны на большее!
Поиск совпадения
Функция REGEXP_INSTR используется для поиска совпадений шаблона в строке. Общий синтаксис REGEXP_INSTR :
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); флаг_возвращаемого_значения — 0 для начальной позиции или 1 для конечной позиции совпадения; модификаторы — один или несколько модификаторов, управляющих процессом поиска (например, i для поиска без учета регистра). Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция REGEXP_INST возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки).
Например, чтобы найти первое вхождение имени, начинающегося с буквы A и завершающегося согласной буквой, можно использовать следующее выражение:
При выполнении этого фрагмента выясняется, что имя на букву A, завершающееся согласной (Andrew), начинается в позиции 22. А вот как проходило построение шаблона:
- A Совпадение начинается с буквы A. Беспокоиться о запятых не нужно — на этой стадии мы уже знаем, что работаем со списком, разделенным запятыми.
- A[a-z ]* За буквой A следует некоторое количество букв или пробелов. Квантификатор * указывает, что за буквой A следует ноль или более таких символов.
- A[a-z ]*[^aeiou] В выражение включается компонент [ ^aeiou ], чтобы имя могло заканчиваться любым символом, кроме гласной. Знак ^ инвертирует содержимое квадратных скобок —
совпадает любой символ, кроме гласной буквы. Так как квантификатор не указан, требуется присутствие ровно одного такого символа. - A[a-z ]*[^aeiou], Совпадение должно завершаться запятой; в противном случае шаблон найдет совпадение для подстроки «An» в имени «Anna». Хотя добавление запятой решает эту проблему, тут же возникает другая: шаблон не найдет совпадение для имени «Aaron» в конце строки.
- A[a-z ]*[^aeiou],|A[a-z ]*[^aeiou]$ В выражении появляется вертикальная черта (|), обозначение альтернативы: общее совпадение находится при совпадении любого из вариантов. Первый вариант завершается запятой, второй — нет. Второй вариант учитывает возможность того, что текущее имя стоит на последнем месте в списке, поэтому он привязывается к концу строки метасимволом $ .
Регулярные выражения — далеко не простая тема! Новички часто сталкиваются с нюансами обработки регулярных выражений, которые часто преподносят неприятные сюрпризы. Я потратил некоторое время на работу над этим примером и несколько раз зашел в тупик, прежде чем выйти на правильный путь. Не отчаивайтесь — с опытом писать регулярные выражения становится проще.
Функция REGEXP_INSTR приносит пользу в некоторых ситуациях, но обычно нас больше интересует текст совпадения, а не информация о его положении в строке.
Получение текста совпадения
Для демонстрации получения текста совпадения мы воспользуемся другим примером. Телефонные номера строятся по определенной схеме, но у этой схемы существует несколько разновидностей. Как правило, телефонный номер начинается с кода города (три цифры), за которым следует код станции (три цифры) и локальный номер (четыре цифры). Таким образом, телефонный номер представляет собой строку из 10 цифр. Однако существует много альтернативных способов представления числа. Код города может быть заключен в круглые скобки и обычно (хотя и не всегда) отделяется от остального номера пробелом, точкой или дефисом. Код станции обычно (но тоже не всегда) отделяется от локального номера пробелом, точкой или дефисом. Таким образом, любая из следующих форм записи телефонного номера является допустимой:
С подобными нежесткими схемами записи легко работать при помощи регулярных выражений, но очень трудно без них. Мы воспользуемся функцией REGEXP_SUBSTR для извлечения телефонного номера из строки с контактными данными:
Результат работы программы:
Ого! Шаблон получился довольно устрашающим. Давайте разобьем его на составляющие:
- \(? Шаблон начинается с необязательного символа открывающей круглой скобки. Так как круглые скобки в языке регулярных выражений являются метасимволами (то есть имеют специальное значение), для использования в качестве литерала символ круглой скобки необходимо экранировать ( escape ), поставив перед ним символ \ (обратная косая черта). Вопросительный знак — квантификатор, обозначающий ноль или одно вхождение предшествующего символа. Таким образом, эта часть выражения описывает необязательный символ открывающей круглой скобки.
- \d \d — один из операторов, появившихся в Oracle10g Release 2 под влиянием языка Perl. Он обозначает произвольную цифру. Квантификатор <> указывает, что предшествующий символ входит в шаблон заданное количество раз (в данном случае три). Эта часть шаблона описывает три цифры.
- \)? Необязательный символ закрывающей круглой скобки.
- [[:space:]\.\-]? В квадратных скобках перечисляются символы, для которых обнаруживается совпадение — в данном случае это пропуск, точка или дефис. Конструкция [ :space: ] обозначает символьный класс POSIX для пропускных символов (пробел, табуляция, новая строка) в текущем наборе NLS. Точка и дефис являются метасимволами, поэтому в шаблоне их необходимо экранировать обратной косой чертой. Наконец, ? означает ноль или одно вхождение предшествующего символа. Эта часть шаблона описывает необязательный пропуск, точку или дефис.
- \d Эта часть шаблона описывает три цифры (см. выше).
- [[:space:]\.\-]? Эта часть шаблона описывает необязательный пропуск, точку или дефис (см. выше).
- \d Четыре цифры (см. выше).
Обязательно комментируйте свой код, использующий регулярные выражения, — это пригодится тому, кто будет разбираться в нем (вполне возможно, это будете вы сами через полгода).
Общий синтаксис REGEXP_SUBSTR :
Функция REGEXP_SUBSTR возвращает часть исходной_строки, совпадающую с шаблоном или подвыражением. Если совпадение не обнаружено, функция возвращает NULL . Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке ; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); модификаторы — один или несколько модификаторов, управляющих процессом поиска.
Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки). Подвыражения удобны в тех случаях, когда требуется найти совпадение для всего шаблона, но получить совпадение только для его части. Скажем, если мы хотим найти телефонный номер и извлечь из него код города, мы заключаем часть шаблона, описывающую код города, в круглые скобки, превращая ее в подвыражение:
Подсчет совпадений
Еще одна типичная задача — подсчет количества совпадений регулярного выражения в строке. До выхода Oracle11g программисту приходилось в цикле перебирать и подсчитывать совпадения. Теперь для этого можно воспользоваться новой функцией REGEXP_COUNT . Общий синтаксис ее вызова:
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска.
Замена текста REGEXP_REPLACE
Поиск и замена — одна из лучших областей применения регулярных выражений. Текст замены может включать ссылки на части исходного выражения (называемые обратными ссылками), открывающие чрезвычайно мощные возможности при работе с текстом. Допустим, имеется список имен, разделенный запятыми, и его содержимое необходимо вывести по два имени в строке. Одно из решений заключается в том, чтобы заменить каждую вторую запятую символом новой строки. Сделать это при помощи стандартной функции REPLACE нелегко, но с функцией REGEXP_REPLACE задача решается просто. Общий синтаксис ее вызова:
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Пример:
Результат выглядит так:
При вызове функции REGEXP_REPLACE передаются три аргумента:
- names — исходная строка;
- '([a-z A-Z]*),([a-z A-Z]*),' — выражение, описывающее заменяемый текст (см. ниже);
- '\1,\2 ' || chr(10) — текст замены. \1 и \2 — обратные ссылки, заложенные в основу нашего решения. Подробные объяснения также приводятся ниже.
Выражение, описывающее искомый текст, состоит из двух подвыражений в круглых скобках и двух запятых.
- ([a-z A-Z]*) Совпадение должно начинаться с имени.
- , За именем должна следовать запятая.
- ([a-z A-Z]*) Затем идет другое имя.
- , И снова одна запятая.
Наша цель — заменить каждую вторую запятую символом новой строки. Вот почему выражение написано так, чтобы оно совпадало с двумя именами и двумя запятыми. Также запятые не напрасно выведены за пределы подвыражений.
Первое совпадение для нашего выражения, которое будет найдено при вызове REGEXP_REPLACE , выглядит так:
Два подвыражения соответствуют именам « Anna » и « Matt ». В основе нашего решения лежит возможность ссылаться на текст, совпавший с заданным подвыражением, через обратную ссылку. Обратные ссылки \1 и \2 в тексте замены ссылаются на текст, совпавший с первым и вторым подвыражением. Вот что происходит:
Вероятно, вы уже видите, какие мощные инструменты оказались в вашем распоряжении. Запятые из исходного текста попросту не используются. Мы берем текст, совпавший с двумя подвыражениями (имена «Anna» и «Matt»), и вставляем их в новую строку с одной запятой и одним символом новой строки.
Но и это еще не все! Текст замены легко изменить так, чтобы вместо запятой в нем использовался символ табуляции (ASCII-код 9):
Теперь результаты выводятся в два аккуратных столбца:
Поиск и замена с использованием регулярных выражений — замечательная штука. Это мощный и элегантный механизм, с помощью которого можно сделать очень многое.
Максимализм и минимализм
Концепции максимализма и минимализма играют важную роль при написании регулярных выражений. Допустим, из разделенного запятыми списка имен нужно извлечь только первое имя и следующую за ним запятую. Список, уже приводившийся ранее, выглядит так:
Казалось бы, нужно искать серию символов, завершающуюся запятой:
Давайте посмотрим, что из этого получится:
Результат выглядит так:
Совсем не то. Что произошло? Дело в «жадности» регулярных выражений: для каждого элемента регулярного выражения подыскивается максимальное совпадение, состоящее из как можно большего количества символов. Когда мы с вами видим конструкцию:
у нас появляется естественное желание остановиться у первой запятой и вернуть строку « Anna ,». Однако база данных пытается найти самую длинную серию символов, завершающуюся запятой; база данных останавливается не на первой запятой, а на последней.
В версии Oracle Database 10g Release 1, в которой впервые была представлена поддержка регулярных выражений, возможности решения проблем максимализма были весьма ограничены. Иногда проблему удавалось решить изменением формулировки регулярного выражения — например, для выделения первого имени с завершающей запятой можно использовать выражение [^,]*, . Однако в других ситуациях приходилось менять весь подход к решению, часто вплоть до применения совершенно других функций.
Начиная с Oracle Database 10g Release 2, проблема максимализма отчасти упростилась с введением минимальных квантификаторов (по образцу тех, которые поддерживаются в Perl ). Добавляя вопросительный знак к квантификатору после точки, то есть превращая * в *?, я ищу самую короткую последовательность символов перед запятой:
Теперь результат выглядит так, как и ожидалось:
Минимальные квантификаторы останавливаются на первом подходящем совпадении, не пытаясь захватить как можно больше символов.
Подробнее о регулярных выражениях
Регулярные выражения на первый взгляд просты, но эта область на удивление глубока и нетривиальна. Они достаточно просты, чтобы вы начали пользоваться ими после прочтения этой статьи (хочется надеяться!), и все же вам предстоит очень много узнать. Некоторые источники информации от компании Oracle и издательства O’Reilly :
Читайте также: