Oracle PL/SQL/Char Functions/LPAD — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Demo LEVEL inside of LPAD
- 2 Demo LEVEL inside of LPAD with "*"
- 3 If the character to be padded is longer than the requested total length, it will be trimmed to the exact total.
- 4 Lpad: makes a string a certain length by adding (padding) a specified set of characters to the left of the original string
- 5 LPAD(string, 10,"*")
- 6 Padding based on value length
- 7 select lpad( "*", 5, "*" )
Demo LEVEL inside of LPAD
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL> select * from employee;
EMP_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE HIREDATE TITLE DEPT_NO MGR REGION DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
1 Anderson Nancy N 33 Ave London NY 11111 1111 212 234-1111 4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager 2 100 10 40000
2 Last First F 12 Ave Paris CA 22222 2222 221 867-2222 8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 1 100 10 10000
3 Wash Georgia G 1 Street14 Barton NJ 33333 3333 214 340-3333 12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer 1 2 100 10 40000
4 Bush Dave D 56 Street Island RI 44444 4444 215 777-4444 22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer 1 2 100 10 40000
5 Will Robin W 56 Street Island MA 55555 5555 216 777-5555 25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer 1 5 100 10 40000
6 Pete Mona M 13 Ave York MO 66666 6666 217 111-6666 9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 5 100 10 40000
7 Roke John J 67 Ave New York BC 77777 7777 218 122-7777 10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant 3 2 100 10 40000
8 Horry Tedi T 1236 Lane Newton NY 88888 8888 219 222-8888 13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative 3 2 100 10 50000
9 Bar Candi C 400 East Street Yorken NY 99999 9999 220 321-9999 12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative 3 5 100 10 35000
9 rows selected.
SQL>
SQL> col name format a20
SQL>
SQL> select lpad(" ",2*(level-1)) || lastname as name
2 from employee
3 start with emp_no = 2
4 connect by prior emp_no = mgr;
NAME
--------------------
Last
Wash
Bush
Roke
Horry
5 rows selected.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
SQL> --
Demo LEVEL inside of LPAD with "*"
SQL>
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL> select * from employee;
EMP_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE HIREDATE TITLE DEPT_NO MGR REGION DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
1 Anderson Nancy N 33 Ave London NY 11111 1111 212 234-1111 4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager 2 100 10 40000
2 Last First F 12 Ave Paris CA 22222 2222 221 867-2222 8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 1 100 10 10000
3 Wash Georgia G 1 Street14 Barton NJ 33333 3333 214 340-3333 12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer 1 2 100 10 40000
4 Bush Dave D 56 Street Island RI 44444 4444 215 777-4444 22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer 1 2 100 10 40000
5 Will Robin W 56 Street Island MA 55555 5555 216 777-5555 25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer 1 5 100 10 40000
6 Pete Mona M 13 Ave York MO 66666 6666 217 111-6666 9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 5 100 10 40000
7 Roke John J 67 Ave New York BC 77777 7777 218 122-7777 10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant 3 2 100 10 40000
8 Horry Tedi T 1236 Lane Newton NY 88888 8888 219 222-8888 13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative 3 2 100 10 50000
9 Bar Candi C 400 East Street Yorken NY 99999 9999 220 321-9999 12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative 3 5 100 10 35000
9 rows selected.
SQL>
SQL> col name format a20
SQL> select lpad("*",level, "*") || lastname as name
2 from employee
3 start with emp_no = 2
4 connect by prior emp_no = mgr;
NAME
--------------------
*Last
**Wash
**Bush
**Roke
**Horry
5 rows selected.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> --
If the character to be padded is longer than the requested total length, it will be trimmed to the exact total.
SQL> SELECT
2 LPAD("ABC",6,"***") result1,
3 LPAD("ABC",4,"***") result2,
4 LPAD("ABC",2,"***") result3
5 FROM dual;
RESULT RESU RE
------ ---- --
***ABC *ABC AB
Lpad: makes a string a certain length by adding (padding) a specified set of characters to the left of the original string
SQL> -- Lpad: makes a string a certain length by adding (padding) a specified set of characters to the left of the original string.
SQL>
SQL> -- LPAD stands for "left pad."
SQL>
SQL> -- The general format for this function is:
SQL>
SQL> -- LPAD(string, length_to_make_string,
SQL> -- what_to_add_to_left_of_string)
SQL>
SQL> SELECT LPAD("Column", 15, ".") FROM dual;
LPAD("COLUMN",1
---------------
.........Column
LPAD(string, 10,"*")
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 LPAD(Last_Name, 10,"*") AS LPAD FROM Employee;
LPAD
----------
****Martin
***Mathews
*****Smith
******Rice
*****Black
*****Green
*****Larry
*******Cat
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Padding based on value length
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 lpad(sal,4)||" "||
2 rpad("o",sal/100,"o") as histogram
3 from emp
4 where deptno = 30;
HISTOGRAM
--------------------------------------------------------------------------------
1600 oooooooooooooooo
1250 oooooooooooo
1250 oooooooooooo
2850 oooooooooooooooooooooooooooo
1500 ooooooooooooooo
800 oooooooo
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
select lpad( "*", 5, "*" )
SQL>
SQL> select lpad( "*", 5, "*" )
2 from dual;
LPAD(
-----
*****
1 row selected.
SQL> --