CODEWITHSUNDEEP
sqloracle-databasesplit
Yuriy T.
Yuriy T.

Reputation: 195

Oracle SQL Split values to columns

What suggestions would be to solve such issue in Oracle SQL scripting:

I have a query, which retrieves for specific PO's it's attachment, and this attachment (as a query result), looks something like that: Project: xxxxxxx Task: yyyyyy Description: rrrrrr Info: qqqqqqq

So basically it's a pre-made set of values.

so my question will be - is it possible to split it somehow per each column instead of having those mentioned values as one cell value, so the mentioned outcome would be: column Project with value xxxx, column task with value yyyyy etc.

I know, that I can use multiple subselects and then use SUBSTR and INSTR functions, but just thinking - is there any other way how to make it quicker? and especially, if such "columns" could be much more and they could dynamically change (e.g. for one there will be task, but for other no)?

Upvotes: 0

Views: 791

Answers (2)

Boneist
Boneist

Reputation: 23578

You can use REGEXP_SUBSTR to do this:

WITH sample_data AS (SELECT 1 ID, 'Project: xxxxxxx Task: yyyyyy Description: rrrrrr Info: qqqqqqq' str FROM dual UNION ALL
                     SELECT 2 ID, 'Project: abcdef Description: fred Info: bloggs' str FROM dual UNION ALL
                     SELECT 3 ID, 'Task: be awesome. Project: the best project ever! Description: my special project Info: only for the best people' str FROM dual UNION ALL
                     SELECT 4 ID, 'Project: Description: james' str FROM dual UNION ALL
                     SELECT 5 ID, 'Project: 151069Task: 99.6Product: Customized something-or-other' str FROM dual)
SELECT ID,
       str,
       regexp_substr(str, 'Project: (.*?)( *Task:| *Description:| *Info:| *$)', 1, 1, NULL, 1) PROJECT,
       regexp_substr(str, 'Task: (.*?)( *Project:| *Description:| *Info:| *$)', 1, 1, NULL, 1) task,
       regexp_substr(str, 'Description: (.*?)( *Project:| *Task:| *Info:| *$)', 1, 1, NULL, 1) description,
       regexp_substr(str, 'Info: (.*?)( *Project:| *Description:| *Task:|$)', 1, 1, NULL, 1) info
FROM   sample_data;

Results:

        ID STR                                                                              PROJECT                                                                          TASK                                                                             DESCRIPTION                                                                      INFO
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Project: xxxxxxx Task: yyyyyy Description: rrrrrr Info: qqqqqqq                  xxxxxxx                                                                          yyyyyy                                                                           rrrrrr                                                                           qqqqqqq
         2 Project: abcdef Description: fred Info: bloggs                                   abcdef                                                                                                                                                            fred                                                                             bloggs
         3 Task: be awesome. Project: the best project ever! Description: my special projec the best project ever!                                                           be awesome.                                                                      my special project                                                               only for the best people
         4 Project: Description: james                                                                                                                                                                                                                        james                                                                            
         5 Project: 151069Task: 99.6Product: Customized something-or-other                  151069                                                                           99.6Product: Customized something-or-other

Each of the regexp_substr's works in the same way. Taking the one generating the Project column, it does:

Find the pattern Project: that's followed by some (or no) characters, which we put inside brackets to determine the first subexpression: (.*?). Then we check for the words Task:, Description:, Info: or the end of the line ($), each of which may or may not be preceded by zero or more spaces (*).

Then we specify the search position and occurrence as 1, the match parameter as null and the output subexpression as 1, since we only want to return the word(s) represented by (.*?) that we defined at the start of the search pattern. And voila!

By doing it that way, it doesn't matter in which order the key words appear in, the regexp_substr will be able to extract the correct word(s) you're after for each key word.

The one caveat is that you need to know how many columns you're expecting to return, but you'd hope that this would be something well defined in your data.

With a delimiter of \n (newline), you could do this instead, which is more flexible:

WITH sample_data AS (SELECT 1 ID, 'Project: xxxxxxx'||chr(10)||'Task: yyyyyy'||chr(10)||'Description: rrrrrr'||chr(10)||'Info: qqqqqqq' str FROM dual UNION ALL
                     SELECT 2 ID, 'Project: abcdef'||chr(10)||'Description: fred'||chr(10)||'Info: bloggs' str FROM dual UNION ALL
                     SELECT 3 ID, 'Task: be awesome.'||chr(10)||'Project: the best project ever!'||chr(10)||'Description: my special project'||chr(10)||'Info: only for the best people' str FROM dual UNION ALL
                     SELECT 4 ID, 'Project:'||chr(10)||'Description: james' str FROM dual UNION ALL
                     SELECT 5 ID, 'Project: 151069'||chr(10)||'Task: 99.6'||chr(10)||'Product: Customized something-or-other' str FROM dual)
SELECT ID,
       str,
       regexp_substr(str, 'Project:( *)(.*)', 1, 1, NULL, 2) PROJECT,
       regexp_substr(str, 'Task:( *)(.*)', 1, 1, NULL, 2) task,
       regexp_substr(str, 'Info:( *)(.*)', 1, 1, NULL, 2) info
FROM   sample_data;

Results:

        ID STR                                                                              PROJECT                                                                          TASK                                                                             INFO
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Project: xxxxxxx                                                                 xxxxxxx                                                                          yyyyyy                                                                           qqqqqqq
           Task: yyyyyy                                                                                                                                                                                                                                       
           Description: rrrrrr                                                                                                                                                                                                                                
           Info: qqqqqqq                                                                                                                                                                                                                                      
         2 Project: abcdef                                                                  abcdef                                                                                                                                                            bloggs
           Description: fred                                                                                                                                                                                                                                  
           Info: bloggs                                                                                                                                                                                                                                       
         3 Task: be awesome.                                                                the best project ever!                                                           be awesome.                                                                      only for the best people
           Project: the best project ever!                                                                                                                                                                                                                    
           Description: my special project                                                                                                                                                                                                                    
           Info: only for the best people                                                                                                                                                                                                                     
         4 Project:                                                                                                                                                                                                                                           
           Description: james                                                                                                                                                                                                                                 
         5 Project: 151069                                                                  151069                                                                           99.6                                                                             
           Task: 99.6                                                                                                                                                                                                                                         
           Product: Customized something-or-other

You could use any character that wouldn't be present in the value for each keyword as a delimiter and use that in place of \n in the last query.

Upvotes: 3

Renato Afonso
Renato Afonso

Reputation: 654

But for what i gather, you have the same character to separate the values. For that, you can use the ListAgg function:

SELECT LISTAGG(column, ': ')
         WITHIN GROUP (ORDER BY name) "List",
   FROM table;

Upvotes: -1

Related Questions