The story isn’t finished yet and I am back to complete it!!! Last year I had done some tutorials on SQL functions and this one is in the continuations of those. So let’s get started.
These two widely used functions return an expression padded with special characters to a specific length. The expression can be a value stored in a column or any user specific expression.
LPAD stands for Left Pad. This function returns an expression padded with the special character to the left side of that expression returned.
RPAD stands for Right Pad. RPAD function returns an expression padded with the special character to the right side of that expression returned.
Apart from the name both the functions share similar syntax.
LPAD (expression/column name, length, padding-expression)
RPAD (expression/column name, length, padding-expression)
Expression/column name: First input is the expression which you want to pad. This can be any mathematics expression, character strings or a column name.
Length: Length is the second parameter, using which you can set the maximum length of the return value as it is displayed on your screen.
Padding-expression: The last parameter is padding expression which is a text expression that specifies the padding characters. The default value of padding expression is a single blank. Here also as a padding expression you can either specify a special character such as asterisk (*) sign, Hash (#) or dollar ($) sign etc. or a mathematics expression or for that matter string. But generally we use special characters.
LPAD and RPAD with Character String as Expression.
SELECT LPAD (‘RebellionRider’, 20, ‘#’) FROM dual;
In this query RebellionRider is our expression which has a length of 14 characters, and hash sign (#) is our padding character which will get added to the left side of the string RebellionRider, and 20 will be the total length of the string.
By Total I mean padding expression + the expression which is a string i.e. RebellionRider in our case.
Let’s try RPAD function in the same query
SELECT RPAD (‘RebellionRider’, 20, ‘#’) FROM dual;
LPAD and RPAD with Column name
SELECT LPAD (salary, 20, ‘*’) FROM employees;
Here in this query we are using values stored in salary column of employees table as the first parameter of LPAD function. We set the total length of the result string on 20 and we are using asterisk sign as our padded character.
Let’s execute the same query with RPAD function
SELECT RPAD (salary, 20, ‘*’) FROM employees;
That’s all guys. Hope this was a good and enjoyable read on LPAD & RPAD functions in SQL for you. Don’t forget to share this on your social & also tag me with that post for a shoutout. Support free IT education. You can watch the YouTube Tutorial on the same for better understanding of the topic.
I will see you soon with another tutorial till then take care. Thanks & Have a great day!