Регулярные выражения Oracle, разбиение строк, поиск подстрок

на 15:42
Ярлыки: , , , , , ,

Довольно часто встречаются вопросы по поиску, разбиению, замене подстрок. Большинство из них можно решить обычными функциями Oracle, в случае же более сложных сложных правил их перестает хватать. Например, если нужно разбить текст на предложения(используя в качестве определения конца предложения точку), не затрагивая предложений внутри цитат, т.е:
Шел дождь. Иванов оглянулся и произнес: "Я пойду за зонтом. Выходи через полчаса."

В Oracle функции регулярных выражений слишком малофункциональны, что достаточно легко обходится с помощью хранимок на java - в java регулярные выражения значительнее функциональнее. Например, в оракловых регулярках нет look-around assertion, а в java - есть. Это и сподвигнуло меня написать пакет, который выполняет данные операции в java stored функциях.

UPD: Внес кое-какие изменения в текст пакетов, актуальные версии выложил на GitHub.

UPD2: Добавил несколько вариантов разбиения строки на подстроки с помощью разделителя

Что этот пакет умеет:
  • function split_simple(p_str in varchar2,p_delim in varchar2)
        return varchar2_table pipelined;

    Это PL/SQL-ная функция, разбивающая не по регулярному выражению, а просто по заданной подстроке. Введена просто для удобства.
  • function split(pStr varchar2,pDelimRegexp varchar2,pMaxCount number default 0)
        return varchar2_table;
    Данная функция разбивает по заданному регулярному выражению, где выражение и является разделителем.Первый параметр строка для разбиения, второй - паттерн регулярного выражения, третий - необязательный - максимальное количество частей, на которые нужно разбить.

    Пример:
    select * from table(
        xt_regexp.split('Пожалуйста, сьешьте еще мягких французских булочек!','(?<=,)\s')
        );

    В примере разбивается по пробелам, перед которыми есть запятая.

    Еще пример:

    select * from table(
        xt_regexp.split('Пожалуйста, сьешьте еще мягких французских булочек!','\s',3)
        );
    Разбиваем по пробелам с максимумом в 3 части


  • function matches(pStr varchar2,pPattern varchar2,
        pCANON_EQ number default 0,
        pCASE_INSENSITIVE number default 0,
        pCOMMENTS         number default 0,
        pDOTALL          number default 0,
        pMULTILINE        number default 0,
        pUNICODE_CAS     number default 0,
        pUNIX_LINES      number default 0
        )
        return boolean;

    Данная функция возвращает true, если в строке находится подстрока удовлетворяющая регулярному выражению. Первый параметр - строка, вторая - регулярка, остальные параметры - это модификаторы рег.выражения:
    • CANON_EQ - каноническое равенство;
    • CASE_INSENSITIVE - игнорирование регистра символов;
    • COMMENTS - режим игнорирования пробелов с разрешением однострочных комментариев в шаблоне с символа #;
    • DOTALL - подразумевать ли под точкой символ новой строки (по умолчанию нет);
    • LITERAL - представлять шаблон как обычную строку, т.е. игнорировать значения спец. символов регулярных выражений как +;
    • MULTILINE - многострочный режим;
    • UNICODE_CASE - регстр букв с учетом юникода;
    • UNIX_LINES - режим юниксовых строк, т.е. разделителем строк является только символ Rn
  • function get_matches(
        pStr varchar2,
        pPattern varchar2,
        pMaxCount number default 0,
            pCANON_EQ number default 0,
            pCASE_INSENSITIVE number default 0,
            pCOMMENTS         number default 0,
            pDOTALL          number default 0,
            pMULTILINE        number default 0,
            pUNICODE_CAS     number default 0,
            pUNIX_LINES      number default 0)
        return varchar2_table;

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

        Пример поиска слов с буквой "е":

    select * from table(xt_regexp.get_matches('Пожалуйста, сьешьте еще мягких французских булочек!','\S*е\S*'));

  • function join_matches(pStr varchar2,pPattern varchar2,pDelim varchar2 default ';',
            pCANON_EQ number default 0,
            pCASE_INSENSITIVE number default 0,
            pCOMMENTS         number default 0,
            pDOTALL          number default 0,
            pMULTILINE        number default 0,
            pUNICODE_CAS     number default 0,
            pUNIX_LINES      number default 0)
        return varchar2;
    Функция аналогична предыдущей - get_matches, но возвращает строку, состоящую из соединенных найденных подстрок через указанный в параметре разделитель.
  • function replace_all(pStr varchar2,pPattern varchar2,pReplacement varchar2)
        return varchar2

    Функция заменяет все подстроки удовлетворяющие регулярному выражению на строку указанную в третьем параметре.

    Пример - заменяем запятые перед которыми слово "Пожалуйста" на "$1 Scott!!!!":
    select xt_regexp.replace_all('Пожалуйста, сьешьте еще мягких французских булочек!','(?<=Пожалуйста)(,)','$1 Scott!!!!') from dual;
    Результат:
    Пожалуйста, Scott!!!! сьешьте еще мягких французских булочек!
  • function replace_first(pStr varchar2,pPattern varchar2,pReplacement varchar2)
        return varchar2
    Данная функция полностью аналогична предыдущей, кроме того, что она заменяет только первое соответствие.

UPD: Добавил функцию longest_overlap - возвращает максимальное совпадение в двух строках:
Пример: select xt_regexp.longest_overlap('1234567890','abcdef3456fgnk') from dual




Примеры использования:

«Click to show Spoiler - click again to hide...»


Несколько вариантов разбиения строк на подстроки по разделителю:
1. С помощью регулярных выражений c 10g:
with t as (
            select 'a,d,f,g,h,j,kassd,asdf,123,455,asd' str,',' delimiter from dual
  union all select '6|7|8|9' str,'|' delimiter from dual
)
select *
from t
     ,table(
         cast(
            multiset(
               select regexp_substr(t.str,'[^'||t.delimiter||',]+',1,level) -- с 10g
               from dual
               connect by regexp_substr(t.str,'[^'||t.delimiter||',]+',1,level) is not null -- c 10g
                        -- для 11g лучше будет:
                        -- level<=regexp_count(t.str,',')
            ) as sys.odcivarchar2list
         )
      ) substring
2. Для древних версий с помощью с substr+instr: 
with t as (
            select 'a,d,f,g,h,j,kassd,asdf,123,455,asd' str,',' delimiter from dual
  union all select '6|7|8|9' str,'|' delimiter from dual
)
select t.*
       ,t2.*
       ,substr(t.str
              ,lag(t2.column_value,1,0)over(partition by t.str order by t2.column_value)+1
              ,t2.column_value-lag(t2.column_value,1,0)over(partition by t.str order by t2.column_value)-1
              )
from t
     ,table(
         cast(
            multiset(
               select instr(t.str||t.delimiter,t.delimiter,1,rownum)
               from all_objects
               where rownum<=1+length(t.str)-length(translate(t.str,'Z'||t.delimiter,'Z'))
            ) as sys.odcinumberlist
         )
      ) t2
3. С помощью парсера xml(ненадежно): 
with t as (
            select 'a,d,f,g,h,j,kassd,asdf,123,455,asd' str,',' delimiter from dual
  union all select '6|7|8|9' str,'|' delimiter from dual
)
select *
from t
     ,xmltable(
         '/rows/*'
         passing xmltype(''||replace(t.str,t.delimiter,'')||'')
         columns 
           rn for ordinality
          ,subst varchar2(4000) path '.'
      ) t2
4. С помощью функций xml(ненадежно): 
with t as (
            select 'a,d,f,g,h,j,kassd,asdf,123,455,asd' str,',' delimiter from dual
  union all select '6|7|8|9' str,'\|' delimiter from dual
)
select *
from t
     ,xmltable(
         ('for $r in ora:tokenize(.,"'||t.delimiter||'") 
             return {$r}')
         passing t.str
         columns subst varchar2(4000) path '.'
      )

Spread The Love, Share Our Article

  • Delicious
  • Digg
  • NewsVine
  • RSS
  • StumbleUpon
  • Technorati
  • Twitter

Comments

Unknown комментирует...

Спасибо за статью, Саян!

В коде для 11g есть небольшая ошибка, не хватает "+1" к кол-ву символов:
-- для 11g лучше будет:
-- level<=regexp_count(t.str,',') + 1
при 2х элементах будет один разделитель, поэтому получится level <= 1 и последний элемент будет исключен. То же верно и для случаев 2+ элементов.

26 сентября 2013 г. в 09:51

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)