Dynamic RPAD() Function using a CASE Expression – Oracle SQL Example

I recently wrote a couple of blog posts on the SQL CASE expression with examples in MySQL. As I study and learn more about Oracle SQL, I wanted to apply a CASE expression to the RPAD() character function for dynamic string padding. Continue reading to see an example query…

wooden-arrow-sign-pointing-right
Image by Gerd Altmann from Pixabay
Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!


The SQL CASE expression is so powerful and I am using it more and more in query expressions where I need that dynamic option.

Practice Data

The fictitious data for this post is stored in a FRIENDS table with a FIRST_NAME column having these values:

SELECT FIRST_NAME FROM FRIENDS;

oracle-sql-query-results-select-all
Query results for all FIRST_NAME column values

We can see each FIRST_NAME and accompanying length of the name with this query using the LENGTH() function:

SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM FRIENDS;

oracle-sql-query-with-length-function
First name and accompanying length.

Create a bind variable

To determine the longest FIRST_NAME column length value, I’ll use the LENGTH() character function on the FIRST_NAME column and wrap the entire expression in a MAX() function call :

SELECT MAX(LENGTH(FIRST_NAME)) AS longest_length_name
FROM FRIENDS;

oracle-sql-length-function
Longest length name value…

Next, I create a bind variable named max_fname_len to store this value for easier use and handling in any subsequent queries:

VAR max_fname_len NUMBER;
BEGIN
SELECT MAX(LENGTH(FIRST_NAME)) INTO :max_fname_len
FROM FRIENDS;
END;
/
print max_fname_len;

oracle-sql-bind-variable-use
Bind Variable Max Name Length.

Consider making a small donation as I continue to provide valuable content here on my blog. Every contribution counts and is very appreciated. Thank you so much!


Dynamic RPAD() using CASE

For this requirement, I am using the RPAD() function to add n number of characters to each ‘first_name’ value depending on if the value is less than the max ‘first_name’ column length value of 7. RPAD() accepts 3 parameters (2 mandatory and 1 optional):

  • target_string – The string to be padded.
  • length – The length of the string after it has been padded with the set pad string.
  • pad string – The string that will be padded to the target_string (in this case, right-padded). If not specified, uses a single space.

By using Searched CASE in the 2nd parameter, the length value is dynamic for each row:

SELECT
RPAD(first_name,
CASE WHEN LENGTH(first_name) < :max_fname_len THEN :max_fname_len - LENGTH(first_name) + LENGTH(first_name) ELSE :max_fname_len END, '*') AS padded_fname
FROM friends;

oracle-sql-dynamic-rpad-using-case-expression
First Name with Dynamic RPAD using CASE Expression.

Notice in the query results, the rows with FIRST_NAME column values ‘Charlie’ and ‘Jupyter’ have not been right-padded since their respective lengths are 7. However, all other FIRST_NAME column values have been right-padded with a number of asterisk characters (*) up to the max_fname_len value of 7.

The row with FIRST_NAME column value of ‘Humpty’ is right-padded with just one asterisk character since the length of that string is 6. Yet, the name ‘Jim’ is right-padded with 4 asterisk characters to make up the length total of 7 because its length is only 3.


Feel free to visit the below posts if you are interested in other similar content:


I understand this example query likely has no real-world use and is purely arbitrary. But, I hope through this blog post, you have an idea of how you can apply CASE expressions for dynamic values when you need them in your queries. Thank you for reading.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.



To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the β€˜Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.


Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

2 thoughts on “Dynamic RPAD() Function using a CASE Expression – Oracle SQL Example

    • Hi Umesh. Thank you very much sir for the compliment on the post. I’m happy you liked it.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.