Oracle PL/SQL/Regular Expressions/Word Start End

Материал из SQL эксперт
Перейти к: навигация, поиск

Add anchors or "wildcard" match characters as need be

   <source lang="sql">

SQL> SQL> -- Add anchors or "wildcard" match characters as need be. SQL> SQL> SELECT REGEXP_INSTR("Hello","^.",2) FROM dual; REGEXP_INSTR("HELLO","^.",2)


                          0

SQL>

      </source>
   
  


"$" a dollar sign is another anchoring operator and matches only the end of a string

   <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 showing"); 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 showing 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> -- "$" a dollar sign is another anchoring operator and matches only the end of a string. 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


-----------

2003 Movie showing 16 SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


"^" "anchoring operator" and matches the beginning of a string

   <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> --"^" "anchoring operator" and matches the beginning of a string. --The caret may also mean "not,". SQL> SQL> SELECT description, REGEXP_INSTR(description,"^O.e") where_it_is

 2  FROM testTable
 3  WHERE REGEXP_INSTR(description,"^O.e") > 0;

DESCRIPTION WHERE_IT_IS


-----------

One than another 1 SQL> SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


Asking for a match for a capital "F" followed by any character

   <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","F003 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 F003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> -- Asking for a match for a capital "F" followed by any character SQL> SQL> SELECT description, REGEXP_INSTR(description,"^F.") where_it_is

 2  FROM testTable
 3  WHERE REGEXP_INSTR(description,"^F.") > 0;

DESCRIPTION WHERE_IT_IS


-----------

F003 Movie 1 SQL> SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>