Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Split 2 comma delimited columns and create rows

I'm trying to produce the following output
col1 col2 aaa qq bbb ww ccc ee
As you can see from my attempt I have hardcoded VALUES, which only produces one row.
I'm looking to get a count from both columns and iterate through the data. If the counts aren't the same for both columns then I want to use a NULL where the count is less and match it with a value.
Below is my simple attempt that is incomplete. Any help would be greatly appreciated.
select regexp_substr('aaa,bbb,ccc', '[^,]+', 1, 1) as col1, regexp_substr('qq, ww, ee', '[^,]+', 1, 1) as col2 from dual
Answers
-
select regexp_substr('aaa,bbb,ccc', '[^,]+', 1, level) as col1, regexp_substr('qq, ww, ee', '[^,]+', 1, level) as col2 from dual connect by level <= greatest( regexp_count('aaa,bbb,ccc', '[^,]+'), regexp_count('qq, ww, ee', '[^,]+') );
Your inputs are inconsistent: the first string has just comma as delimiter, but the second has comma + space. If that's also your real-life data, the solution shown above will preserve the leading spaces; you may trim them in the
select
list if needed.Will all the tokens be non-empty? Or can the first string look like
'xxx,,zzz'
(indicating that the middle token is the empty string, the same asNULL
in Oracle)? If there can be empty-string (NULL
) tokens, your approach will not include them in the output - and perhaps mess up the ordering (thenzzz
would be paired with the second token from the other comma-separated string, instead of the last token). If that is a concern, you must change the+
quantifier to*
in all places.I didn't want to assume the two comma-separated list have the same number of tokens; if they always do, you can simplify the
CONNECT BY
condition, you don't need theGREATEST
function there.This will work if you have just two "matched" comma-separated strings as input. If you have a table with several rows, and in each row you have two such comma-separated strings, and you need a query that will split all those comma-separated strings keeping track of the matching, the query will be a bit more complicated (but not by much). Anyway, I am leaving that out for now, since you didn't say anything about that.