PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References
PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References

PostgreSQL LPAD() Function



The PostgreSQL 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.

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

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

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

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

SELECT LPAD('', 8, 'XYZ');
Result: 'XYZXYZXY'

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

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

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCitySalary
1JohnLondon3000
2MarryNew York2750
3JoParis2800
4KimAmsterdam3100
5RameshNew Delhi3000
6HuangBeijing2800

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

UPDATE Employee 
SET EmpID = LPAD(CAST(EmpID AS VARCHAR), 4, 'FIN');

-- see the result
SELECT * FROM Employee;

This will produce the following result:

EmpIDNameCitySalary
FIN1JohnLondon3000
FIN2MarryNew York2750
FIN3JoParis2800
FIN4KimAmsterdam3100
FIN5RameshNew Delhi3000
FIN6HuangBeijing2800

❮ PostgreSQL Functions