LPAD in SQL - Scaler Topics

Overwiew

The LPAD in SQL, one of the SQL String Functions is a function that helps in padding or adding a string to the left of the given string. The LPAD in SQL takes a string as an input (i.e. the string that has to be padded) and pads another string to the left, and returns a new string.

How to Use the LPAD Function in SQL?

The following is the syntax of LPAD in SQL:

Syntax

inputStr: The inputStr refers to the string to which another string is to be padded.
padStr: The padStr refers to the string which will be padded to the left of the inputStr.
length: It refers to the length of the final string after padding is done.

Note: If the length of the original string is larger than the length parameter, this function removes the over-floating characters from the padString.

Return value:

It returns a new string in which the padStr is padded to the left of inputStr.

Examples of LPAD Function in SQL

Example 1: Appending a String to the Left of Another String

Code:

Output:

Explanation of the example:

In the above example, the input string is "Parker", and the string to be padded is "Peter ". Thus after appending the two strings, we will get "Peter Parker". Also, we can observe that the length of the obtained string is 12, which is equal to the specified length. Thus the final output will be "Peter Parker".

Example 2: Appending a String to the Left of Another String (When the Resultant String is Larger than the Specified Length)

Code:

Output:

Explanation of the example:

In the above example, the input string is "Wayne", and the string to be padded is "Bruce ". Thus after appending the two strings, we should have got "Bruce Wayne". But we can observe that the length of the obtained string will be 11, which is not equal to the specified length. Thus the LPAD will remove the last two letters of the padStr and then append it to the left of the input string, and the output will be 'BrucWayne'.

Example 3: Appending a String to the Left of Another String (When the Resultant string is Smaller than the Specified Length)

Code:

Output:

Explanation of the example:

In the above example, the input string is "Stark", and the string to be padded is "Tony". Thus after appending the two strings, we should have got "Tony Stark". But we can observe that the length of the obtained string will be 10, which is not equal to the specified length. Thus the LPAD will add the first three to the padStr and then append it to the left of the input string, and the output will be 'Tony TonStark'.

Learn more about Advanced Concepts of SQL

In order to learn more about advanced concepts of SQL, visit this link.

Conclusion

LPAD in SQL is used to append a string to the left of another string.
LPAD in SQL takes three parameters.
- The first input is the input string to the left of which the other string is to be added.
- The second input is the padString which is the string that is to be padded to the left of the input string.
- The third argument is the length of the final string. If the resultant string is not of the same length as the specified length, then the padding string is either trimmed or incremented.