Oracle PL/SQL Tutorial/Character String Functions/INSTR
Содержание
- 1 Display the position where the second occurrence of e occurs in employee"s first name
- 2 If search pattern is not in the string, the INSTR function returns 0
- 3 If the INSTR pattern is not found, then the entire string would be returned
- 4 instr and substr
- 5 INSTR ("in-string") is a function used to find patterns in strings
- 6 INSTR returns a location within the string where search pattern begins.
- 7 INSTR(x, find_string [, start] [, occurrence]) searchs for find_string in x
- 8 Look for the second occurrence of "is"
- 9 Use dates in INSTR functions
- 10 Using SUBSTR and INSTR together
Display the position where the second occurrence of e occurs in employee"s first name
The following example displays the position where the second occurrence of e occurs, starting from the beginning of the first_name using INSTR()
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT first_name, INSTR(first_name, "e", 1, 2) FROM employee; FIRST_NAME INSTR(FIRST_NAME,"E",1,2)
-------------------------
Jason 0 Alison 0 James 0 Celia 0 Robert 0 Linda 0 David 0 James 0 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
If search pattern is not in the string, the INSTR function returns 0
<source lang="sql">
SQL> SQL> SELECT INSTR("This is a test","abc",1,2)FROM dual
2</source>
If the INSTR pattern is not found, then the entire string would be returned
<source lang="sql">
SQL> SELECT SUBSTR("H, J E", INSTR("H,J E","z")) FROM dual; SUBSTR
H, J E SQL></source>
instr and substr
<source lang="sql">
SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK 3 primary key 4 , dname VARCHAR2(10) 5 , location VARCHAR2(8) 6 , mgr NUMBER(4) 7 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> SQL> col substr2 format a7 SQL> select dname
2 , substr(dname,4) as substr1 3 , substr(dname,4,3) as substr2 4 , instr(dname,"I") as instr1 5 , instr(dname,"I",5) as instr2 6 , instr(dname,"I",3,2) as instr3 7 from departments;
DNAME SUBSTR1 SUBSTR2 INSTR1 INSTR2 INSTR3
------- ------- ---------- ---------- ----------
ACCOUNTING OUNTING OUN 8 8 0 TRAINING INING INI 4 6 6 SALES ES ES 0 0 0 HR 0 0 0 SQL> SQL> drop table departments; Table dropped.</source>
INSTR ("in-string") is a function used to find patterns in strings
- The general syntax of INSTR is:
- INSTR (string to search, search pattern [, start [,occurrence]])
- The arguments within brackets ([]) are optional.
11. 6. INSTR 11. 6. 1. <A href="/Tutorial/Oracle/0220__Character-String-Functions/INSTRxfindstringstartoccurrencesearchsforfindstringinx.htm">INSTR(x, find_string [, start] [, occurrence]) searchs for find_string in x</a> 11. 6. 2. INSTR ("in-string") is a function used to find patterns in strings 11. 6. 3. <A href="/Tutorial/Oracle/0220__Character-String-Functions/Displaythepositionwherethesecondoccurrenceofeoccursinemployeesfirstname.htm">Display the position where the second occurrence of e occurs in employee"s first name</a> 11. 6. 4. <A href="/Tutorial/Oracle/0220__Character-String-Functions/INSTRreturnsalocationwithinthestringwheresearchpatternbegins.htm">INSTR returns a location within the string where search pattern begins.</a> 11. 6. 5. <A href="/Tutorial/Oracle/0220__Character-String-Functions/Lookforthesecondoccurrenceofis.htm">Look for the second occurrence of "is"</a> 11. 6. 6. <A href="/Tutorial/Oracle/0220__Character-String-Functions/IfsearchpatternisnotinthestringtheINSTRfunctionreturns0.htm">If search pattern is not in the string, the INSTR function returns 0</a> 11. 6. 7. <A href="/Tutorial/Oracle/0220__Character-String-Functions/UsingSUBSTRandINSTRtogether.htm">Using SUBSTR and INSTR together</a> 11. 6. 8. <A href="/Tutorial/Oracle/0220__Character-String-Functions/IftheINSTRpatternisnotfoundthentheentirestringwouldbereturned.htm">If the INSTR pattern is not found, then the entire string would be returned</a> 11. 6. 9. <A href="/Tutorial/Oracle/0220__Character-String-Functions/UsedatesinINSTRfunctions.htm">Use dates in INSTR functions</a> 11. 6. 10. <A href="/Tutorial/Oracle/0220__Character-String-Functions/instrandsubstr.htm">instr and substr</a>
INSTR returns a location within the string where search pattern begins.
<source lang="sql">
SQL> SQL> SELECT INSTR("This is a test","is")FROM dual
2 /
INSTR("THISISATEST","IS")
3
SQL></source>
INSTR(x, find_string [, start] [, occurrence]) searchs for find_string in x
INSTR() returns the position at which find_string occurs.
The "start position" is optional.
The "start position" specifies the position to start the search in x.
The "Occurrence" is also optional.
The "Occurrence" indicates which occurrence of find_string should be returned.
The following example selects the first_name column and displays the position where the string J occurs in the name column.
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT first_name, INSTR(first_name, "J") FROM employee; FIRST_NAME INSTR(FIRST_NAME,"J")
---------------------
Jason 1 Alison 0 James 1 Celia 0 Robert 0 Linda 0 David 0 James 1 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Look for the second occurrence of "is"
<source lang="sql">
SQL> SQL> SELECT INSTR("This is a test","is",1,2)FROM dual
2 /
INSTR("THISISATEST","IS",1,2)
6
SQL></source>
Use dates in INSTR functions
The following example displays the position where the string JAN occurs in start_date.
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT id, start_date, INSTR(start_date, "JAN") FROM employee; ID START_DAT INSTR(START_DATE,"JAN")
--------- -----------------------
01 25-JUL-96 0 02 21-MAR-76 0 03 12-DEC-78 0 04 24-OCT-82 0 05 15-JAN-84 4 06 30-JUL-87 0 07 31-DEC-90 0 08 17-SEP-96 0 8 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Using SUBSTR and INSTR together
<source lang="sql">
SQL> SELECT SUBSTR("H, J E", INSTR("H, J E",", ")+2) FROM dual; SUB --- J E SQL></source>
