Oracle PL/SQL/Char Functions/SUBSTR
Содержание
- 1 select substr( "1234567890", 5 )
- 2 Simple demo for SUBSTR function
- 3 SUBSTR and INSTR are used together
- 4 substr(date value,8)+16
- 5 SUBSTR: from the right-hand side of original string
- 6 SUBSTR: get the sub-string
- 7 Substr: retrieve a portion of the string
- 8 SUBSTR() returns a portion of string, beginning at numeric_position up to a specified substring_length characters long.
- 9 use substr in IF statement
- 10 Use SUBSTR to decode a column
select substr( "1234567890", 5 )
SQL>
SQL>
SQL> select substr( "1234567890", 5 )
2 from dual
3 /
SUBSTR
------
567890
1 row selected.
SQL>
SQL> --
Simple demo for SUBSTR function
SQL>
SQL>
SQL> SELECT SUBSTR("My address is 123 Fourth St.",1,12) FROM dual;
SUBSTR("MYAD
------------
My address i
SQL>
SUBSTR and INSTR are used together
SQL>
SQL> -- SUBSTR and INSTR are used together
SQL>
SQL> SELECT SUBSTR("aaa, bb ccc", INSTR("aaa, bb ccc",", ")+2) FROM dual;
SUBSTR
------
bb ccc
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: from the right-hand side of original string
SQL>
SQL> -- SUBSTR: from the right-hand side of original string:
SQL>
SQL> SELECT SUBSTR("My address is 123 Fourth St.",-9,5) FROM dual;
SUBST
-----
ourth
SQL>
SUBSTR: get the sub-string
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> --Return the first initial and last name:
SQL>
SQL> SELECT SUBSTR(First_Name, 1, 1) , Last_Name FROM Employee;
S LAST_NAME
- ----------
J Martin
A Mathews
J Smith
C Rice
R Black
L Green
D Larry
J Cat
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Substr: retrieve a portion of the string
SQL> -- Substr: retrieve a portion of the string.
SQL>
SQL> -- SUBSTR(string, start_at_position, number_of_characters_to_retrieve)
SQL>
SQL> SELECT SUBSTR("Mississippi", 5, 3) FROM dual;
SUB
---
iss
SUBSTR() returns a portion of string, beginning at numeric_position up to a specified substring_length characters long.
SQL>
Syntax: SUBSTR(<string_expression>, <numeric_position> [,<substring_length>])
SQL>
SQL> SELECT
2 SUBSTR("HELLO, WORLD", 1,5) first_word,
3 SUBSTR("HELLO, WORLD", 8) last_word,
4 SUBSTR("HELLO, WORLD",-5) back
5 FROM dual;
FIRST LAST_ BACK
----- ----- -----
HELLO WORLD WORLD
SQL>
use substr in IF statement
SQL>
SQL>
SQL> DECLARE
2 lv_training_code_txt VARCHAR2(10) := "T_Code";
3 lv_non_training_code_txt VARCHAR2(10) := "TUSC";
4 PROCEDURE training_class_check (p_class_check VARCHAR) IS p_class_check1 VARCHAR2(10) := p_class_check;
5 BEGIN
6 IF SUBSTR(p_class_check1,1,2) = "T_" THEN
7 p_class_check1 := "T-" || SUBSTR(p_class_check1, 3);
8 END IF;
9 IF p_class_check1 LIKE "T-%" THEN
10 DBMS_OUTPUT.PUT_LINE(p_class_check ||" is a Training Class");
11 ELSE
12 DBMS_OUTPUT.PUT_LINE(p_class_check ||" is a Non-Training Class");
13 END IF;
14 END training_class_check;
15
16 BEGIN
17 training_class_check(lv_training_code_txt);
18 training_class_check(lv_non_training_code_txt);
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Use SUBSTR to decode a column
SQL>
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
SQL>
SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-103", "Shipping Carton, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-104", "Shipping Carton, Large");
1 row created.
SQL>
SQL> SELECT SUBSTR(item_id, 1, 2) MFGR_LOCATION,
2 SUBSTR(item_id, 4, 3) ITEM_NUMBER,
3 item_desc
4 FROM old_item;
MF ITE ITEM_DESC
-- --- -------------------------
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
MF ITE ITEM_DESC
-- --- -------------------------
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
MF ITE ITEM_DESC
-- --- -------------------------
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
32 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
SQL>