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:
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));
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>