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
<source lang="sql">
SQL> SELECT SUBSTR("My address is 123 Fourth St.",6) FROM dual; SUBSTR("MYADDRESSIS123F
dress is 123 Fourth St. SQL></source>
Combine DECODE and SUBSTR together
<source lang="sql">
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></source>
Combine SUBSTR and INSTR to deal with a table column
<source lang="sql">
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></source>
Compare substring in where statement
<source lang="sql">
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></source>
Example SELECT output using SUBSTR().
<source lang="sql">
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></source>
If begin is negative, then retrieval occurs from the right-hand side of original string
<source lang="sql">
SQL> SELECT SUBSTR("My address is 123 Fourth St.",-9,5)FROM dual; SUBST
ourth SQL></source>
Manipulating Strings: SUBSTR and concatenation
<source lang="sql">
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.</source>
Retrieval begins at position 5 and again goes for 12 characters
<source lang="sql">
SQL> SELECT SUBSTR("My address is 1234 Fourth St.",5,12)FROM dual; SUBSTR("MYAD
ddress is 12 SQL></source>
SUBSTR and INSTR used together
<source lang="sql">
SQL> SELECT SUBSTR("H, J E", INSTR("H, J E",", ")+2) FROM dual; SUB --- J E SQL></source>
substr(,) and substr(,,)
<source lang="sql">
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.</source>
substr birthday
<source lang="sql">
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.</source>
substr(date value,8)+16
<source lang="sql">
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.</source>
Substr retrieves a portion of the string
The general format for this function is:
SUBSTR(string, start_at_position, number_of_characters_to_retrieve)
<source lang="sql">
SQL> SQL> SELECT SUBSTR("Mississippi", 5, 3) FROM dual; SUB --- iss SQL></source>
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:
<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> 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.</source>
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.
<source lang="sql">
SQL> SELECT SUBSTR("My address is 123 Fourth St.",1,12)FROM dual
2 /
SUBSTR("MYAD
My address i SQL></source>
