Выборка подстроки по шаблону

Функция

Поиск в строке подстроки по заданному шаблону.

Спецификация
 
Общие правила
  1. < Символьное выражение > в < строке > может иметь следующие типы данных: CHAR, VARCHAR, NCHAR, NCHAR VARYING.

  2. < Символьный литерал > в < шаблоне > должен быть регулярным выражением с форматом, аналогичным формату регулярного выражения функции REGEXP_LIKE.

  3. Длина < подстроки > не должна быть более 4000.

  4. < Начало поиска > задает начальную позицию для поиска < подстроки > в символах. Отсчет начинается с единицы. Если < начало поиска > не задано, по умолчанию принимается значение 1.

  5. < Номер вхождения > задает порядковый номер искомой подстроки. Отсчет начинается с единицы. Если < номер вхождения > не задан, по умолчанию принимается значение 1.

  6. < Числовое выражение > в < начале поиска > или в < номере вхождения > должно иметь целый тип или приводиться к нему.

  7. < Сопоставление > – модификатор, изменяющий стандартный механизм сопоставления символьных данных:

    • 'c' – сопоставление, чувствительное к регистру символов (по умолчанию);

    • 'i' – сопоставление, нечувствительное к регистру символов.

Возвращаемое значение
  1. Подстрока, удовлетворяющая заданному < шаблону > или NULL-значение, если подстрока не найдена. Тип возвращаемого значения определяется типом первого аргумента функции.

Примеры
  1. Выделение ссылки на сайт:
    
    SELECT REGEXP_SUBSTR('See http://relex.ru/ru/documentation/ for details', 'https?://[^/]*');
    
    Результат выполнения примера:
    |http://relex.ru                                  |
  2. Выделение подстрок из строки с разделителем ('|'):
    
    select
      REGEXP_SUBSTR('one|two|three|four|five', '[^\|]+', 1, 1),
      REGEXP_SUBSTR('one|two|three|four|five', '[^\|]+', 1, 2),
      REGEXP_SUBSTR('one|two|three|four|five', '[^\|]+', 1, 3),
      REGEXP_SUBSTR('one|two|three|four|five', '[^\|]+', 1, 4),
      REGEXP_SUBSTR('one|two|three|four|five', '[^\|]+', 1, 5);
    
    Результат выполнения примера:
    
    |one                    |two                    |three                  |four
                    |five                   |
  3. create or replace table tab25574_phone03 (id int, fio char(40), mobile_phone char(15), check(REGEXP_LIKE(mobile_phone, '[^a-zA-z]')));
    
    !Ошибка
    insert into tab25574_phone03 values(1,'aaa','ABC');
    
    !Нормальное завершение
    insert into tab25574_phone03 values(1,'aaa','123');
    
    !30|T|:
    select REGEXP_LIKE('http://www.linter.ru', 'http://([[:alnum:]]+\.?){3,4}');
    |T|:
    
    select REGEXP_LIKE('http://www.linter.ru/', 'http://([[:alnum:]]+\.?){3,4}/?');
    |T|:
    
    
    select REGEXP_SUBSTR('http://www.linter.ru/documentation', 'http://([[:alnum:]]+\.?){3,4}/?');
    !http://www.linter.ru/'
  4. create or replace table regtest(dt char (50));
    insert into regtest values('Иванов, 01011982, Воронеж');
    insert into regtest values('Зайцев, 01111998, Киев');
    insert into regtest values('Петров, 01011988, Москва');
    insert into regtest values('Волков, 010A1988, Дмитров');
    select REGEXP_SUBSTR(t.dt,'[0-9]{8}') rr,t.dt rr from regtest t;
    
    select regexp_substr('first field, second field , third field', ', [^,]*,');
    
    |01011982   |Иванов, 01011982, Воронеж                         |
    |01111998   |Зайцев, 01111998, Киев                            |
    |01011988   |Петров, 01011988, Москва                          |
    |NULL       |Волков, 010A1988, Дмитров                         |
    
    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);
    
    |01000|7961400000|0|0|0|001|0|K0101|0|0|0|0|
  5. CREATE OR REPLACE TABLE t1 (data VARCHAR(50));
    INSERT INTO t1 VALUES ('FALL 2014');
    INSERT INTO t1 VALUES ('2014 CODE-B');
    INSERT INTO t1 VALUES ('CODE-A 2014 CODE-D');
    INSERT INTO t1 VALUES ('ADSHLHSALK');
    INSERT INTO t1 VALUES ('FALL 2004');
    
    SELECT * FROM t1 WHERE  TO_NUMBER(REGEXP_SUBSTR(data, '\d{4}'))  >= 2014;
    
    |FALL 2014                                         |
    |2014 CODE-B                                       |
    |CODE-A 2014 CODE-D                                |
    
    delete from t1;
    INSERT INTO t1 VALUES ('ArtADB1234567e9876540');
    SELECT REGEXP_SUBSTR(data, '[A-Z][a-z]+', 1, 1) col1,
           REGEXP_SUBSTR(data, '[A-Z]+', 1, 2) col2,
           REGEXP_SUBSTR(data, '[0-9]+', 1, 1) col3,
           REGEXP_SUBSTR(data, '[a-z]+', 1, 2) col4,
           REGEXP_SUBSTR(data, '[0-9]+', 1, 2) col5
      FROM t1;
    
    |Art |ADB |1234567 |e  |9876540 |
    
    delete from t1;
    
    INSERT INTO t1 VALUES ('978/955086/GZ120804/10-FEB-12');
    INSERT INTO t1 VALUES ('97/95508/BANANA/10-FEB-12');
    INSERT INTO t1 VALUES ('97/95508/"APPLE"/10-FEB-12');
    SELECT REGEXP_SUBSTR(data, '[^/"]+', 1, 3) AS element3 FROM t1;
    
    |GZ120804                                          |
    |BANANA                                            |
    |APPLE                                             |
    
    drop table t1;
    select regexp_substr('We are driving south by south east','south');
    |south                             |
    
    SELECT regexp_substr('655-236-4567', '-[^-]+' );
    |-236        |
    SELECT regexp_substr('655-236-4567', '-[^\-]+' );
    |-236        |
    SELECT regexp_substr( '655-236-4567', '-[^-]+-' );
    |-236-       |
    
    SELECT regexp_substr('Employee Name and Age: Adam, Dana 28', ':');
    |:                                   |
    SELECT regexp_substr('Employee Name and Age: Adam, Dana 28', ':[^,]+');
    |: Adam                              |
    SELECT regexp_substr('Employee Name and Age: Adam, Dana 28', '[[:digit:]]+' );
    |28                                  |
    
    SELECT REGEXP_SUBSTR('Hey! Yababa dababa do!','(ab)\1');
    |abab                  |
    
    SELECT REGEXP_SUBSTR('ab12cd','[0-9]+');
    |12    |
    
    SELECT REGEXP_SUBSTR('We are trying to make the subject
     easier.','tr(y(ing)?|(ied)|(ies))');
    |trying                                   |
    
    SELECT REGEXP_SUBSTR('system/pwd@orabase:1521:sidval','[^:]+', 1, 3);
    |sidval                        |
    
    select REGEXP_SUBSTR('1:3,4:6,8:10,3:4,7:6,11:12','[^:]+', 1, 1);
    |1                         |
    
    select REGEXP_SUBSTR('1:3,4:6,8:10,3:4,7:6,11:12','[^,]+', 3, 1);
    |3                         |
  6. CREATE OR REPLACE TABLE test (name VARCHAR(10), version VARCHAR(15));
    INSERT INTO test values ('A', '1');
    INSERT INTO test values ('B', '12.1.0.2');
    INSERT INTO test values ('B', '8.2.1.2');
    INSERT INTO test values ('B', '12.0.0');
    INSERT INTO test values ('C', '11.1.2');
    INSERT INTO test values ('C', '11.01.05');
    
    select REGEXP_SUBSTR(version, '\d+', 1, 1) from test;
    |1              |
    |12             |
    |8              |
    |12             |
    |11             |
    |11             |
    
    select REGEXP_SUBSTR(version, '\d+', 1, 2) from test;
    |NULL               |
    |1              |
    |2              |
    |0              |
    |1              |
    |01             |
    
    select regexp_like('abac','(a.){2}'), regexp_like('abbc','(a.){2}');
    |T|F|
    
    select REGEXP_LIKE('sdfsdsfsd.as.sdsd@jhkk.d.rl', '^[a-z0-9](\.?[a-z0-9_\-]){0,}@[a-z0-9\-]+\.([a-z]{1,6}\.)?[a-z]{2,6}$');
    |T|
    
    select substring('This image is nice' similar 'T%\"i[[:alpha:]]+e\"%is [[:alnum:]]+' escape '\');
    |image|