в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:11:35Z

1 версия

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

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

<OL><LI>The general syntax of INSTR is:</LI><LI>INSTR (string to search, search pattern [, start [,occurrence]])</LI><LI>The arguments within brackets ([]) are optional.</LI></OL>
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>

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

Oracle PL/SQL Tutorial/Character String Functions/INSTR - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия