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…
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.
The fictitious data for this post is stored in a FRIENDS table with a FIRST_NAME column having these values:
We can see each FIRST_NAME and accompanying length of the name with this query using the LENGTH() function:
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 :
Next, I create a bind variable named max_fname_len to store this value for easier use and handling in any subsequent queries:
SELECT MAX(LENGTH(FIRST_NAME)) INTO :max_fname_len
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:
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
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:
- Import CSV file with Oracle SQL Developer
- SUBSTR() Character Function — Oracle SQL Example
- How to find the first and last day of a month with Oracle SQL Date Functions
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.
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.