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()
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>
If search pattern is not in the string, the INSTR function returns 0
SQL>
SQL> SELECT INSTR("This is a test","abc",1,2)FROM dual
2
If the INSTR pattern is not found, then the entire string would be returned
SQL> SELECT SUBSTR("H, J E", INSTR("H,J E","z")) FROM dual;
SUBSTR
------
H, J E
SQL>
instr and substr
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.
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.
SQL>
SQL> SELECT INSTR("This is a test","is")FROM dual
2 /
INSTR("THISISATEST","IS")
-------------------------
3
SQL>
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.
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>
Look for the second occurrence of "is"
SQL>
SQL> SELECT INSTR("This is a test","is",1,2)FROM dual
2 /
INSTR("THISISATEST","IS",1,2)
-----------------------------
6
SQL>
Use dates in INSTR functions
The following example displays the position where the string JAN occurs in start_date.
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>
Using SUBSTR and INSTR together
SQL> SELECT SUBSTR("H, J E", INSTR("H, J E",", ")+2) FROM dual;
SUB
---
J E
SQL>