Oracle PL/SQL/Regular Expressions/REGEXP INSTR

Материал из SQL эксперт
Версия от 10:02, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Apecify any series of letters and find matches, just like INSTR

 

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, Description) values("1","1234 5th Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("3","1234 Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("5","One than another");
1 row created.
SQL> insert into TestTable (ID, Description) values("6","2003 Movie");
1 row created.
SQL> insert into TestTable (ID, Description) values("7","Starting");
1 row created.
SQL>
SQL> select * from TestTable;
ID   DESCRIPTION
---- ----------------------------------------
1    1234 5th Street
2    1 Culloden Street
3    1234 Road
4    33 Thrid Road
5    One than another
6    2003 Movie
7    Starting
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- Apecify any series of letters and find matches, just like INSTR:
SQL>
SQL> SELECT description, REGEXP_INSTR(description,"ing") where_it_is
  2  FROM testTable
  3  WHERE REGEXP_INSTR(description,"ing") > 0;
DESCRIPTION                              WHERE_IT_IS
---------------------------------------- -----------
Starting                                           6
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>



"i": to match case insensitively

 
SQL>
SQL> -- Find the "s" and ignore case.
SQL>
SQL> SELECT REGEXP_INSTR("Sam told a story","s",1,1,0,"i") position FROM dual;
  POSITION
----------
         1
SQL>



REGEXP_INSTR("abc","d",1,1,1)

 
SQL>
SQL> SELECT REGEXP_INSTR("abc","d",1,1,1) FROM dual;
REGEXP_INSTR("ABC","D",1,1,1)
-----------------------------
                            0



REGEXP_INSTR("abc","d?"): include the "?" repetition character

 
SQL>
SQL> -- if we include the "?" repetition character, we get this seemingly odd result:
SQL>
SQL> SELECT REGEXP_INSTR("abc","d?") FROM dual;
REGEXP_INSTR("ABC","D?")
------------------------
                       1
SQL>



regexp_instr(comments, "[^ ]+", 1, 9)

  
SQL>
SQL> create table vacation
  2  ( empno      NUMBER(4)
  3  , beginyear  NUMBER(4)
  4  , begindate  DATE
  5  , enddate    DATE
  6  , deptno     NUMBER(2)
  7  , sal        NUMBER(6,2)
  8  , comments   VARCHAR2(60)
  9  , constraint H_PK         primary key (empno,begindate)
 10  , constraint H_BEG_END    check       (begindate < enddate)
 11  ) ;
Table created.
SQL>
SQL>
SQL> alter session  set NLS_DATE_FORMAT="DD-MM-YYYY";
Session altered.
SQL>
SQL> insert into vacation values (1,2000,"01-01-2000","01-02-2000",40, 950,"");
1 row created.
SQL> insert into vacation values (1,2000,"01-02-2000", NULL       ,20, 800,"restarted");
1 row created.
SQL> insert into vacation values (2,1995,"01-10-1995","01-11-2009",30,1700,"");
1 row created.
SQL> insert into vacation values (2,2009,"01-11-2009", NULL       ,30,1600,"just hired");
1 row created.
SQL> insert into vacation values (3,1986,"01-10-1986","01-08-1987",20,1000,"");
1 row created.
SQL> insert into vacation values (3,1987,"01-08-1987","01-01-1989",30,1000,"On training");
1 row created.
SQL>
SQL> select comments
  2  from   vacation
  3  where  regexp_instr(comments, "[^ ]+", 1, 9) > 0;
no rows selected
SQL>
SQL>
SQL> drop table vacation;
Table dropped.



REGEXP_INSTR(description,"ee") > 0

 

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(30 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, Description) values("1","1234 5th Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("3","1234 Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("5","One than another");
1 row created.
SQL> insert into TestTable (ID, Description) values("6","2003 Movie");
1 row created.
SQL> insert into TestTable (ID, Description) values("7","Start With Letters");
1 row created.
SQL>
SQL> select * from TestTable;
ID   DESCRIPTION
---- ------------------------------
1    1234 5th Street
2    1 Culloden Street
3    1234 Road
4    33 Thrid Road
5    One than another
6    2003 Movie
7    Start With Letters
7 rows selected.
SQL>
SQL>
SQL> -- More Advanced Quantifier Repeat Operator Metacharacters
SQL>
SQL> SELECT description,
  2      REGEXP_INSTR(description,"ee") where_pattern_starts
  3  FROM testTable
  4  WHERE REGEXP_INSTR(description,"ee") > 0;
DESCRIPTION                    WHERE_PATTERN_STARTS
------------------------------ --------------------
1234 5th Street                                  13
1 Culloden Street                                15
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>



REGEXP_INSTR function in where clause

 

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, Description) values("1","1234 5th Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("3","1234 Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("5","One than another");
1 row created.
SQL> insert into TestTable (ID, Description) values("6","2003 Movie");
1 row created.
SQL> insert into TestTable (ID, Description) values("7","Start With Letters");
1 row created.
SQL>
SQL> select * from TestTable;
ID   DESCRIPTION
---- ----------------------------------------
1    1234 5th Street
2    1 Culloden Street
3    1234 Road
4    33 Thrid Road
5    One than another
6    2003 Movie
7    Start With Letters
7 rows selected.
SQL>
SQL>
SQL> -- REGEXP_INSTR(String to search, Pattern, [Position, [Occurrence, [Return-option, [Parameters]]]])
SQL>
SQL> SELECT Description, REGEXP_INSTR(Description,"Road") where_it_is FROM TestTable
  2  WHERE REGEXP_INSTR(Description,"Road") > 0;
DESCRIPTION                              WHERE_IT_IS
---------------------------------------- -----------
1234 Road                                          6
33 Thrid Road                                     10
SQL>
SQL>
SQL>
SQL> drop table TestTable;



regexp_instr function summary

 
regexp_instr (string, pattern)
regexp_instr (string, pattern, position)
regexp_instr (string, pattern, position, occurence)
regexp_instr (string, pattern, position, occurence, return-option)
regexp_instr (string, pattern, position, occurence, return-option, parameters)
parameters can be a combination of
    * i: to match case insensitively
    * c: to match case sensitively
    * n: to make the dot (.) match new lines as well
    * m: to make ^ and $ match beginning and end of a line in a multiline string
    * x: to ignore white spaces. 
regexp_instr is an Oracle SQL function that enables regular expressions in queries. 
It enhances the "traditional" instr.



REGEXP_INSTR("Mississippi", "si", 1,2,0,"i")

 
SQL>
SQL> SELECT REGEXP_INSTR("Mississippi", "si", 1,2,0,"i") FROM dual;
REGEXP_INSTR("MISSISSIPPI","SI",1,2,0,"I")
------------------------------------------
                                         7
SQL>



regexp_instr (string, pattern)

 
SQL>
SQL> SELECT REGEXP_INSTR("444 Oneway drive","One") where_it_is FROM dual;
WHERE_IT_IS
-----------
          5
SQL>



regexp_instr (string, pattern, position, occurence, return-option)

 
SQL>
SQL> -- The default (0) beginning of the position where the pattern is found:
SQL>
SQL> SELECT REGEXP_INSTR("Mary has a cold","a",1,2,0) position FROM dual;
  POSITION
----------
         7
SQL>



regexp_instr (string, pattern, position, occurence, return-option, parameters): c: to match case sensitively

 
SQL>
SQL>
SQL> -- Find the "s" and match case.
SQL>
SQL> SELECT REGEXP_INSTR("Sam told a story","s",1,1,0,"c") position FROM dual;
  POSITION
----------
        12
SQL>



Return-option is set to 1 to indicate the end of the found pattern

 
SQL>
SQL>
SQL> -- Return-option is set to 1 to indicate the end of the found pattern:
SQL>
SQL> SELECT REGEXP_INSTR("Mary has a cold","a",1,2,1) position FROM dual;
  POSITION
----------
         8
SQL>



Returns the position of the second occurrence that matches the letter "o" starting at position 10

 
SQL>
SQL>
SQL> -- Returns the position of the second occurrence that matches the letter o starting at position 10 using REGEXP_INSTR():
SQL>
SQL> SELECT REGEXP_INSTR("www.sqle.ru Oracle",  "o", 10, 2) AS result FROM dual;
    RESULT
----------
         0
SQL>
SQL>



Simple demo for REGEXP_INSTR

 
SQL> SELECT REGEXP_INSTR("Mary has a cold","a") position FROM dual;
  POSITION
----------
         2
SQL>



The simplest regular expression matches letters, letter for letter

 

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, Description) values("1","1234 5th Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street");
1 row created.
SQL> insert into TestTable (ID, Description) values("3","1234 Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road");
1 row created.
SQL> insert into TestTable (ID, Description) values("5","One than another");
1 row created.
SQL> insert into TestTable (ID, Description) values("6","2003 Movie");
1 row created.
SQL> insert into TestTable (ID, Description) values("7","Start With Letters");
1 row created.
SQL>
SQL> select * from TestTable;
ID   DESCRIPTION
---- ----------------------------------------
1    1234 5th Street
2    1 Culloden Street
3    1234 Road
4    33 Thrid Road
5    One than another
6    2003 Movie
7    Start With Letters
7 rows selected.
SQL>
SQL>
SQL>
SQL> -- The simplest regular expression matches letters, letter for letter
SQL>
SQL> SELECT REGEXP_INSTR(Description,"Road") One FROM testtable WHERE REGEXP_INSTR(Description,"Road") > 0;
       ONE
----------
         6
        10
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Regular_Expressions/REGEXP_INSTR&oldid=2645»