Главная > SQL > Извлечь подстроки используя разделители


Извлечь подстроки используя разделители

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

SQL> SELECT regexp_substr(str, '[^;]+', 1, level) str
  2  FROM (
  3        SELECT ' 1; 2; test1.' str FROM dual     ) t
  4  CONNECT BY instr(str, ';', 1, level - 1) > 0
  5  ;

STR
-------------
 1
 2
 test1.

Если хотим разбить на слова используя в виде разделителя пробел, то можно использовать perl синтаксис:

SQL> SELECT regexp_substr(str, '\S+', 1, level) str
  2  FROM (
  3        SELECT ' 1 2 test1.' str FROM dual     ) t
  4  connect by regexp_substr(str,'\S+',1,level) is not null
  5  ;

STR
-----------
1
2
test1.

Другие примеры…

Если требуется разделить не только на строки, но и на столбцы:

SQL> column str1 format a10;
SQL> column str2 format a10;
SQL> column str3 format a10;
SQL> select
  2    regexp_substr(str, '[^=]+', 1, 1) as str1,
  3    regexp_substr(str, '[^=]+', 1, 2) as str2,
  4    regexp_substr(str, '[^=]+', 1, 3) as str3
  5  from (
  6  select regexp_substr(str, '[^@]+', 1, level) str
  7  from (
  8  select rtrim('field1=field2=field3@field3=field4@field5=field6=field7@','@') str
  9  from dual)
 10  CONNECT BY REGEXP_INSTR (str, '@', 1, level - 1) > 0
 11       );

STR1       STR2       STR3
---------- ---------- ----------
field1     field2     field3
field3     field4     
field5     field6     field7

или

  select regexp_substr(str, '[^\.]+', 1, 1 )  str1
        ,regexp_substr(str, '[^\.]+', 1, 2 )  str2
        ,regexp_substr(str, '[^\.]+', 1, 3 )  str3
        ,regexp_substr(str, '[^\.]+', 1, 4 )  str4
        ,regexp_substr(str, '[^\.]+', 1, 5 )  str5
        ,regexp_substr(str, '[^\.]+', 1, 6 )  str6
        ,regexp_substr(str, '[^\.]+', 1, 7 )  str7
        ,regexp_substr(str, '[^\.]+', 1, 8 )  str8
        ,regexp_substr(str, '[^\.]+', 1, 9 )  str9
        ,regexp_substr(str, '[^\.]+', 1, 10) str10
        ,regexp_substr(str, '[^\.]+', 1, 11) str11
        ,regexp_substr(str, '[^\.]+', 1, 12) str12
  from (
  select '01000.7961400000.0.0.0.001.0.K0101.0.0.0.0' str
  from dual)

Пример разбора на лексемы для нескольких строк

SQL> with t as (
  2  select 'a;б;в' as str, 1 as id from dual
  3  union all
  4  select 'г;д;' as str, 2  from dual
  5  union all
  6  select null as str, 3 from dual
  7  union all
  8  select 'p' as str, 4 from dual
  9  )
 10  select regexp_substr(str, '[^;]+', 1, level) str2 , t.* from t
 11  CONNECT BY instr(trim(';' from str), ';', 1, level - 1) > 0
 12  and prior id = id
 13  and prior dbms_random.value is not null
 14  ;

STR2       STR           ID
---------- ----- ----------
a          a;б;в          1
б          a;б;в          1
в          a;б;в          1
г          г;д;           2
д          г;д;           2
                          3
p          p              4

7 rows selected

Пример разбора формулы

SQL> with t as
  2  (
  3   select 'a+b-v-s' str from dual
  4  )
  5  select case when level != 1 then regexp_substr(str, '[*+|-]', 1, level-1)
  6         end prefix_sign,
  7         regexp_substr(str, '[^+|-]+', 1, level) str,
  8         regexp_substr(str, '[*+|-]', 1, level) postfix_sign
  9  from t
 10  CONNECT BY regexp_substr(str, '[^+|-]+', 1, level) is not null;

PREFIX_SIGN STR     POSTFIX_SIGN
----------- ------- ------------
            a       +
+           b       -
-           v       -
-           s

Похожие записи:

  1. Сергей
    5 Февраль 2014 в 12:16 | #1

    Спасибо, очень помогло

  2. Виктор
    22 Июнь 2016 в 13:35 | #2

    Очень полезная информация, спасибо!

  3. Григорий
    6 Февраль 2017 в 11:18 | #3

    «Пример разбора на лексемы для нескольких строк» не работает, если между двумя раделителями пусто, например ‘г;д;’ разбивается на лексемы также, как и ‘г;;д’

  4. Григорий
    6 Февраль 2017 в 12:58 | #4

    «Пример разбора на лексемы для нескольких строк» не работает, если между двумя раделителями пусто, например ‘г;д;’ разбивается на лексемы также, как и ‘г;;д’
    Чтобы гарантировать наличие символов между разделителями «;», можно после разделитемя с помощью replace вставить что-нибудь «ненужное» (пробел), а потом с помощью substr( … ,2 ) убрать это «ненужное»

    with t as
    (select ‘a;б;в’ as str, 1 as id from dual union all
    select ‘г;д;’ as str, 2 from dual union all
    select ‘г;;д’ as str, 3 from dual union all
    select null as str, 4 from dual union all
    select ‘p’ as str, 5 from dual )

    select t.*,
    regexp_substr(str, ‘[^;]+’, 1, level) str2,
    substr(regexp_substr(replace(‘;’||str, ‘;’, ‘; ‘), ‘[^;]+’, 1, level),2) str3

    from t
    CONNECT BY instr(trim(‘;’ from str), ‘;’, 1, level — 1) > 0
    and prior id = id
    and prior dbms_random.value is not null;

  5. Александр
    16 Декабрь 2017 в 23:56 | #5

    Большое спасибо за полезную информацию!

  6. Дмитрий
    14 Май 2019 в 00:16 | #6

    Спасибо, очень полезная информация!

  7. Сергей
    18 Июнь 2019 в 15:45 | #7

    Не могу понять как применить это в моём случае.
    Есть строка HR||166666|…
    и есть строка HR|2777777|166666|…
    Вопрос в том как составить шаблон для regexp_substr чтобы и в первом и во втором случае по одному шаблону и номеру вхождения, выбирало 3ю секцию — 166666.
    Чтобы regexp_substr(str, /*шаблон*/ , 1, 3) давало и в первом и во втором случае 166666.
    Заранее спасибо за ответ!

  1. 9 Январь 2011 в 14:11 | #1
  2. 19 Сентябрь 2013 в 00:04 | #2