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
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>