Oracle PL/SQL Tutorial/Character String Functions/SUBSTR
Содержание
- 1 A retrieval with no third argument: it starts at begin and retrieves the rest of the string
- 2 Combine DECODE and SUBSTR together
- 3 Combine SUBSTR and INSTR to deal with a table column
- 4 Compare substring in where statement
- 5 Example SELECT output using SUBSTR().
- 6 If begin is negative, then retrieval occurs from the right-hand side of original string
- 7 Manipulating Strings: SUBSTR and concatenation
- 8 Retrieval begins at position 5 and again goes for 12 characters
- 9 SUBSTR and INSTR used together
- 10 substr(,) and substr(,,)
- 11 substr birthday
- 12 substr(date value,8)+16
- 13 Substr retrieves a portion of the string
- 14 SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start.
- 15 The SUBSTR Function
A retrieval with no third argument: it starts at begin and retrieves the rest of the string
SQL> SELECT SUBSTR("My address is 123 Fourth St.",6) FROM dual;
SUBSTR("MYADDRESSIS123F
-----------------------
dress is 123 Fourth St.
SQL>
Combine DECODE and SUBSTR together
SQL>
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 DECODE(SUBSTR(first_name, 1, 2),
2 "Ja", "Western",
3 "Ro", "Eastern",
4 "* Unknown *"
5 ) "Name"
6 FROM employee;
Name
-----------
Western
* Unknown *
Western
* Unknown *
Eastern
* Unknown *
* Unknown *
Western
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Combine SUBSTR and INSTR to deal with a table column
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) ,
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 description, SUBSTR(description, 1, INSTR(description, "r", 1 ) -1 )FROM employee;
DESCRIPTION SUBSTR(DESCRIPT
--------------- ---------------
Programmer P
Tester Teste
Tester Teste
Manager Manage
Tester Teste
Tester Teste
Manager Manage
Tester Teste
8 rows selected.
SQL>
SQL>
SQL> drop table Employee
2 /
Table dropped.
SQL>
Compare substring in where statement
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL> SELECT last_name, first_name
2 FROM employee
3 WHERE substr(employee_id,1,5) = 76031;
no rows selected
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Example SELECT output using SUBSTR().
SQL>
SQL> select substr(owner,1,10) owner,
2 substr(object_name,1,10) object_name,
3 substr(object_type, 1,10) object_type,
4 to_char(last_ddl_time,"dd-mon-yyyy hh24:mi") last_ddl_time
5 from dba_objects
6 where owner="SYSTEM" and rownum < 50;
OWNER OBJECT_NAM OBJECT_TYP LAST_DDL_TIME
---------- ---------- ---------- -----------------
SYSTEM SYSCATALOG SYNONYM 07-feb-2006 22:12
SYSTEM CATALOG SYNONYM 07-feb-2006 22:12
SYSTEM TAB SYNONYM 07-feb-2006 22:12
SYSTEM COL SYNONYM 07-feb-2006 22:12
SYSTEM TABQUOTAS SYNONYM 07-feb-2006 22:12
SYSTEM SYSFILES SYNONYM 07-feb-2006 22:12
SYSTEM PUBLICSYN SYNONYM 07-feb-2006 22:12
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV INDEX 07-feb-2006 22:13
SYSTEM MVIEW$_ADV TABLE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV SEQUENCE 07-feb-2006 22:13
SYSTEM MVIEW$_ADV SEQUENCE 07-feb-2006 22:13
49 rows selected.
SQL>
SQL>
SQL>
If begin is negative, then retrieval occurs from the right-hand side of original string
SQL> SELECT SUBSTR("My address is 123 Fourth St.",-9,5)FROM dual;
SUBST
-----
ourth
SQL>
Manipulating Strings: SUBSTR and concatenation
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT
2 SUBSTR(Name, INSTR(Name, " ") + 1) || ", " ||
3 SUBSTR(Name, 1, INSTR(Name, " ") - 1) AS empName
4 FROM emp
5 ORDER BY empName;
EMPNAME
--------------------------------------------------------------------------------
Bill,
Cat,
Jack,
Mary,
Tom,
Victor,
6 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
Retrieval begins at position 5 and again goes for 12 characters
SQL> SELECT SUBSTR("My address is 1234 Fourth St.",5,12)FROM dual;
SUBSTR("MYAD
------------
ddress is 12
SQL>
SUBSTR and INSTR used together
SQL> SELECT SUBSTR("H, J E", INSTR("H, J E",", ")+2) FROM dual;
SUB
---
J E
SQL>
substr(,) and substr(,,)
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2);
1 row created.
SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 4);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 5);
1 row created.
SQL>
SQL>
SQL>
SQL> select dname
2 , substr(dname,4) as substr1
3 , substr(dname,4,3) as substr2
4 from departments;
DNAME SUBSTR1 SUBSTR2
---------- ------- -------
ACCOUNTING OUNTING OUN
TRAINING INING INI
SALES ES ES
HR
SQL>
SQL> drop table departments;
Table dropped.
substr birthday
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> select ename, substr(bdate,8)+16
2 from employees;
ENAME SUBSTR(BDATE,8)+16
-------- ------------------
Jason 81
Jerry 82
Jord 83
Mary 84
Joe 85
Black 86
Red 87
White 88
Yellow 89
Pink 90
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
substr(date value,8)+16
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> select ename, substr(bdate,8)+16
2 from emp
3 where deptno = 10;
ENAME SUBSTR(BDATE,8)+16
-------- ------------------
Chris 981
Peter 968
Mike 978
SQL>
SQL> drop table emp;
Table dropped.
Substr retrieves a portion of the string
The general format for this function is:
SUBSTR(string, start_at_position, number_of_characters_to_retrieve)
SQL>
SQL> SELECT SUBSTR("Mississippi", 5, 3) FROM dual;
SUB
---
iss
SQL>
SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start.
You can also provide an optional length for the substring.
The following example uses SUBSTR() to select a two-character substring starting at position 2 from the first name column of the employee table:
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>
SQL> SELECT SUBSTR(first_name, 2, 2) FROM employee;
SU
--
as
li
am
el
ob
in
av
am
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
The SUBSTR Function
The SUBSTR function returns part of a string.
The general syntax of the function is as follows:
SUBSTR(original string, begin [,how far])
An original string is to be dissected beginning at the begin character.
If "how far" amount is not specified, then the rest of the string from the begin point is retrieved.
If begin is negative, then retrieval occurs from the right-hand side of original string.
SQL> SELECT SUBSTR("My address is 123 Fourth St.",1,12)FROM dual
2 /
SUBSTR("MYAD
------------
My address i
SQL>