Oracle PL/SQL/Regular Expressions/REGEXP INSTR
Содержание
- 1 Apecify any series of letters and find matches, just like INSTR
- 2 "i": to match case insensitively
- 3 REGEXP_INSTR("abc","d",1,1,1)
- 4 REGEXP_INSTR("abc","d?"): include the "?" repetition character
- 5 regexp_instr(comments, "[^ ]+", 1, 9)
- 6 REGEXP_INSTR(description,"ee") > 0
- 7 REGEXP_INSTR function in where clause
- 8 regexp_instr function summary
- 9 REGEXP_INSTR("Mississippi", "si", 1,2,0,"i")
- 10 regexp_instr (string, pattern)
- 11 regexp_instr (string, pattern, position, occurence, return-option)
- 12 regexp_instr (string, pattern, position, occurence, return-option, parameters): c: to match case sensitively
- 13 Return-option is set to 1 to indicate the end of the found pattern
- 14 Returns the position of the second occurrence that matches the letter "o" starting at position 10
- 15 Simple demo for REGEXP_INSTR
- 16 The simplest regular expression matches letters, letter for letter
Apecify any series of letters and find matches, just like INSTR
<source lang="sql">
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>
</source>
"i": to match case insensitively
<source lang="sql">
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>
</source>
REGEXP_INSTR("abc","d",1,1,1)
<source lang="sql">
SQL> SQL> SELECT REGEXP_INSTR("abc","d",1,1,1) FROM dual; REGEXP_INSTR("ABC","D",1,1,1)
0
</source>
REGEXP_INSTR("abc","d?"): include the "?" repetition character
<source lang="sql">
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>
</source>
regexp_instr(comments, "[^ ]+", 1, 9)
<source lang="sql">
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.
</source>
REGEXP_INSTR(description,"ee") > 0
<source lang="sql">
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>
</source>
REGEXP_INSTR function in where clause
<source lang="sql">
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;
</source>
regexp_instr function summary
<source lang="sql">
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.
</source>
REGEXP_INSTR("Mississippi", "si", 1,2,0,"i")
<source lang="sql">
SQL> SQL> SELECT REGEXP_INSTR("Mississippi", "si", 1,2,0,"i") FROM dual; REGEXP_INSTR("MISSISSIPPI","SI",1,2,0,"I")
7
SQL>
</source>
regexp_instr (string, pattern)
<source lang="sql">
SQL> SQL> SELECT REGEXP_INSTR("444 Oneway drive","One") where_it_is FROM dual; WHERE_IT_IS
5
SQL>
</source>
regexp_instr (string, pattern, position, occurence, return-option)
<source lang="sql">
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>
</source>
regexp_instr (string, pattern, position, occurence, return-option, parameters): c: to match case sensitively
<source lang="sql">
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>
</source>
Return-option is set to 1 to indicate the end of the found pattern
<source lang="sql">
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>
</source>
Returns the position of the second occurrence that matches the letter "o" starting at position 10
<source lang="sql">
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>
</source>
Simple demo for REGEXP_INSTR
<source lang="sql">
SQL> SELECT REGEXP_INSTR("Mary has a cold","a") position FROM dual;
POSITION
2
SQL>
</source>
The simplest regular expression matches letters, letter for letter
<source lang="sql">
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>
</source>
