Suppose you have a comma-separated list composed of numbers and you want it sorted in order from smallest to largest. Continue reading and see a working example I came up with as I continue studying Oracle SQL…
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!
Note: As with anything, just because you can do something doesn’t necessarily mean you should do it. This post is mainly a reflection and documentation for my future self of learning some of the regex functions the Oracle database provides.
I have a simple table with one column of the VARCHAR2 datatype:
FROM A_STR_TBL;
The single row of data is a comma-separated list of non-sequential numbers. If you want to order the numbers, there are likely several ways you can accomplish that. I’m using some of the regexp (regular expression) functions for this example.
Determine the count of elements using REGEXP_COUNT()
The first thing you need to know is how many elements are in the list. You can determine this by counting the number of commas and adding 1 to that value. In the following query, I use the REGEXP_COUNT() function and return the count of commas present in the list.
FROM A_STR_TBL;
To iterate through the list of numbers and return 1 element per row, I use the CONNECT BY clause in conjunction with the number of commas returned by REGEXP_COUNT().
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',');
Notice there is 1 element missing from the query results. Because I am using REGEXP_COUNT() to iterate through the list a number of times equal to the count of commas, I need to add 1 to this value. Recall, there are 6 commas but 7 elements.
Same query as above, but this time I add 1 to the overall number returned by REGEXP_COUNT() in order to iterate the correct number of times.
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',') + 1;
Using REGEXP_SUBSTR() to split the list into individual elements
In this next query, the REGEXP_SUBSTR() function is used to split the list on the comma, returning each individual number element. Using the to_number() function, I covert each element to an actual integer number value.
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',') + 1;
For easier handling, I use a WITH clause and create a temporary result set to further SELECT from and sort the list elements in ASC order:
SELECT to_number(REGEXP_SUBSTR(some_str, '[^,]+', 1, level)) nums
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',') + 1)
SELECT *
FROM bunch_of_nums
ORDER BY nums ASC;
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
Rebuild the comma-separated list using LISTAGG()
I now use the LISTAGG() and to_char() functions to create a comma-separated list of the sorted numbers.
SELECT to_number(REGEXP_SUBSTR(some_str, '[^,]+', 1, level)) nums
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',') + 1)
SELECT
to_char(LISTAGG(nums, ',')
WITHIN GROUP (ORDER BY nums ASC)) sorted_some_str
FROM bunch_of_nums;
Bonus: Inserting data using the WITH clause
One great feature (of many) of the WITH clause (Common Table Expression or subquery factoring clause) is we can have multiple subqueries within the same CTE by separating each AS section with a comma. I leverage this feature in the below INSERT statement using the WITH clause as the data source for an INSERT with SELECT type of query:
WITH bunch_of_nums AS (
SELECT to_number(REGEXP_SUBSTR(some_str, '[^,]+', 1, level)) nums
FROM A_STR_TBL
CONNECT BY level <= REGEXP_COUNT(some_str, ',') + 1),
sorted_str AS (
SELECT
to_char(LISTAGG(nums, ',')
WITHIN GROUP (ORDER BY nums ASC)) sorted_some_str
FROM bunch_of_nums)
SELECT sorted_some_str
FROM sorted_str;
Once the INSERT has been executed, I now have the original comma-separated list sorted in sequential order and stored in the A_STR_TBL table:
This is my first encounter with Oracle’s REGEXP_SUBSTR() and REGEXP_COUNT() functions. I know as I work more with the database, I will likely use them again. If you see any mistakes or have questions about the code, please share freely in the comments. Constructive feedback helps me provide accurate articles and is much appreciated!
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.
Amazing blog 😀😀☺️
Hi and thank you very much for the compliment and I’m glad you like the post 🙏👏👍