CODEWITHSUNDEEP
sqloracle-database
dang
dang

Reputation: 2412

Split column value into dynamic columns

I have a table table1 with the following column:

file_name
dqID.AB_RES_ZEST_H2O_XYZ.LKG774548_00035_CAB_TS_HEST_H99_WED_45430098138.txt_154853452388.txt.xml
dqID.AB_RES_ZEST_H2O_XYZ.LKG744348_022335_CAB_TS_HEST_H99_WED_445438138.txt_15344452388.txt.xml
dqID.AB_RES_ZEST_H2O_XYZ.LKG774548_000435_CAB_TS_HEST_H99_WED_45345138.txt_15485343458.txt.xml

I'm looking to create dynamic number of columns based on the values and split using _ and .

So, the output would be:

file_name                                                                                                  part1    part2   part3   part4......
dqID.AB_RES_ZEST_H2O_XYZ.LKG774548_00035_CAB_TS_HEST_H99_WED_45430098138.txt_154853452388.txt.xml           dqID     AB     RES   ZEST....
dqID.AB_RES_ZEST_H2O_XYZ.LKG744348_022335_CAB_TS_HEST_H99_WED_445438138.txt_15344452388.txt.xml             dqID     AB     RES   ZEST....
dqID.AB_RES_ZEST_H2O_XYZ.LKG774548_000435_CAB_TS_HEST_H99_WED_45345138.txt_15485343458.txt.xml              dqID     AB     RES   ZEST....

Is there anyway in Oracle SQL to split the text into multiple parts using 2 _ and .

Also, create part columns dynamically? Because the string can contain either a lot of _ & . or very few _ & .

Upvotes: 0

Views: 579

Answers (1)

GMB
GMB

Reputation: 222402

As commented, a sql query must return a fixed set of columns, so the dynamic part cannot be performed in pure SQL.

Here is a solution that uses REGEXP_SUBSTR() to split the string over a fixed number of columns:

SELECT
    file_name,
    REGEXP_SUBSTR(file_name, '[^._]+', 1, 1) as part1,
    REGEXP_SUBSTR(file_name, '[^._]+', 1, 2) as part2,
    REGEXP_SUBSTR(file_name, '[^._]+', 1, 3) as part3
FROM table1

You can add as many columnms as needed in the resultset.

Regexp explanation:

[             # any character
    ^         #     other than
        .     #         a dot
        _     #         or an underscore
]             #
+             # occuring at least once

So basically this will capture string portions in between separators . and _.

Upvotes: 3

Related Questions