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 )
<source lang="sql">
SQL> SQL> SQL> select substr( "1234567890", 5 )
2 from dual 3 /
SUBSTR
567890 1 row selected. SQL> SQL> --
</source>
Simple demo for SUBSTR function
<source lang="sql">
SQL> SQL> SQL> SELECT SUBSTR("My address is 123 Fourth St.",1,12) FROM dual; SUBSTR("MYAD
My address i SQL>
</source>
SUBSTR and INSTR are used together
<source lang="sql">
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
</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: from the right-hand side of original string
<source lang="sql">
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>
</source>
SUBSTR: get the sub-string
<source lang="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> --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>
</source>
Substr: retrieve a portion of the string
<source lang="sql">
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
</source>
SUBSTR() returns a portion of string, beginning at numeric_position up to a specified substring_length characters long.
<source lang="sql">
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>
</source>
use substr in IF statement
<source lang="sql">
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>
</source>
Use SUBSTR to decode a column
<source lang="sql">
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>
</source>
