в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:11Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Regular Expressions/REGEXP INSTR - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия