Oracle LPAD() Function

The Oracle (PL/SQL) LPAD() function returns a string that is left-padded with a specified string to a certain length. If the string is longer than length, this function will remove characters from the string to shorten it to the length characters.

Syntax

LPAD(string, length, pad_string)

Parameters

`string`	`Required.` Specify the string to left-pad.
`length`	`Required.` Specify the length of the result after the string has been left-padded.
`pad_string`	`Optional.` Specify the string to left-pad to the string. If omitted, this function pads spaces.

Return Value

Returns a string that is left-padded with a specified string to a certain length.

Example 1:

The example below shows the usage of LPAD() function.

LPAD('alphacodingskills', 21)
Result: '    alphacodingskills'

LPAD('alphacodingskills', 21, ' ')
Result: '    alphacodingskills'

LPAD('alphacodingskills', 21, '*')
Result: '****alphacodingskills'

LPAD('alphacodingskills', 21, 'XYZ')
Result: 'XYZXalphacodingskills'

LPAD('abc', 8, 'XYZ')
Result: 'XYZXYabc'

LPAD('alphacodingskills', 11, 'XYZ')
Result: 'alphacoding'

Example 2:

Consider a database table called Employee with the following records:

EmpID	Name	City	Salary
1	John	London	3000
2	Marry	New York	2750
3	Jo	Paris	2800
4	Kim	Amsterdam	3100
5	Ramesh	New Delhi	3000
6	Huang	Beijing	2800

The below mentioned query is used to left-pad the records of EmpID column of the Employee table:

SELECT Employee.*, 
LPAD(EmpID, 4, 'FIN') AS NewEmpID 
FROM Employee;

This will produce the following result:

EmpID	Name	City	Salary	NewEmpID
1	John	London	3000	FIN1
2	Marry	New York	2750	FIN2
3	Jo	Paris	2800	FIN3
4	Kim	Amsterdam	3100	FIN4
5	Ramesh	New Delhi	3000	FIN5
6	Huang	Beijing	2800	FIN6

❮ Oracle Functions