This Oracle tutorial explains how to use the Oracle/PLSQL LPAD function.
LPAD adds a specified set of characters to the left of the original string.
LPAD stands for "left pad". The general format for this function is:
LPAD(string, length_to_make_string[, what_to_add_to_left_of_string])
By default the LPAD adds space to the value:
The syntax for the Oracle/PLSQL LPAD function is:
LPAD( string1, padded_length, [ pad_string ] )
string1 is the string to pad characters to the left-hand side.
padded_length is the number of characters to return.
If the padded_length is smaller than the original string, the LPAD
function will truncate the string to the size of padded_length.
pad_string is optional. Default to spaces.
SQL> SELECT LPAD('Column', 15) FROM dual;
LPAD('COLUMN',1
---------------
Column
SQL>
Adding dot to the value:
SQL> SELECT LPAD('Column', 15, '.') FROM dual;
LPAD('COLUMN',1
---------------
.........Column
SQL>
The following sql adds dots to the employee's name:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w ww . ja v a2 s . co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select ename, lpad(ename,10),lpad(ename,10,'.') from emp;
ENAME LPAD(ENAME LPAD(ENAME
---------- ---------- ----------
SMITH SMITH .....SMITH
ALLEN ALLEN .....ALLEN
WARD WARD ......WARD
JONES JONES .....JONES
MARTIN MARTIN ....MARTIN
BLAKE BLAKE .....BLAKE
CLARK CLARK .....CLARK
SCOTT SCOTT .....SCOTT
KING KING ......KING
TURNER TURNER ....TURNER
ADAMS ADAMS .....ADAMS
11 rows selected.
SQL>
The number value is lpadable:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- ww w . j a v a2 s . c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select ename, lpad(SAL,10),lpad(SAL,10,'.') from emp;
ENAME LPAD(SAL,1 LPAD(SAL,1
---------- ---------- ----------
SMITH 800 .......800
ALLEN 1600 ......1600
WARD 1250 ......1250
JONES 2975 ......2975
MARTIN 1250 ......1250
BLAKE 2850 ......2850
CLARK 2850 ......2850
SCOTT 3000 ......3000
KING 3000 ......3000
TURNER 1500 ......1500
ADAMS 1500.12 ...1500.12
11 rows selected.
SQL>