Turn one column into multiple columns by key words-CodePudding

How can I split a column into more columns base on the specific words? For example, I have table A and I want to split col wherever the words "AND, OR, PLUS" appears, so that I get table B as the result.

ID	col
1	THE BIG APPLE AND ORANGE OR PEAR
2	BANNANA EATS GRAPE OR BLUEBERRY
3	THE BEST FRUIT IS WATERMELON
4	FRUITS OR CANDY ARE THE BEST OR WATER
5	APPLE STRAWBERRY AND PLUM PLUS SUGAR OR PEACH
6	MELON IN MY BELLY

ID	col1	col2	col3	col4
1	THE BIG APPLE	ORANGE	PEAR
2	BANNANA EATS GRAPE	BLUEBERRY
3	THE BEST FRUIT IS WATERMELON
4	FRUITS	CANDY ARE THE BEST	WATER
5	APPLE STRAWBERRY	PLUM	SUGAR	PEACH
6	MELON IN MY BELLY

CodePudding user response：

You can split the string and then PIVOT:

SELECT *
FROM   (
  SELECT id,
         idx,
         match
  FROM   table_name
         CROSS APPLY (
           SELECT LEVEL AS idx,
                  REGEXP_SUBSTR(
                    col,
                    '(. ?)(\s (AND|OR|PLUS)\s |$)',
                    1,
                    LEVEL,
                    'i',
                    1
                  ) AS match
           FROM   DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT(
                    col,
                    '(. ?)(\s (AND|OR|PLUS)\s |$)',
                    1,
                    'i'
                  )
         )
)
PIVOT (
  MAX(match)
  FOR idx IN (1 AS col1, 2 AS col2, 3 AS col3, 4 AS col4)
);

Note: SQL statements MUST have a fixed number of output columns so you cannot dynamically set the number of columns with a static SQL statement. It would possibly be better to just use the inner query (without the outer wrapper which performs the PIVOT) and output the values as rows rather than columns and then if you want to transpose to columns then do it in whatever front-end you are using to access the database.

Which, for the sample data:

CREATE TABLE table_name (ID, col) AS
SELECT 1, 'THE BIG APPLE AND ORANGE OR PEAR' FROM DUAL UNION ALL
SELECT 2, 'BANNANA EATS GRAPE OR BLUEBERRY' FROM DUAL UNION ALL
SELECT 3, 'THE BEST FRUIT IS WATERMELON' FROM DUAL UNION ALL
SELECT 4, 'FRUITS OR CANDY ARE THE BEST OR WATER' FROM DUAL UNION ALL
SELECT 5, 'APPLE STRAWBERRY AND PLUM PLUS SUGAR OR PEACH' FROM DUAL UNION ALL
SELECT 6, 'MELON IN MY BELLY' FROM DUAL;

Outputs:

ID COL1 COL2 COL3 COL4

1 THE BIG APPLE ORANGE PEAR null

2 BANNANA EATS GRAPE BLUEBERRY null null

3 THE BEST FRUIT IS WATERMELON null null null

4 FRUITS CANDY ARE THE BEST WATER null

5 APPLE STRAWBERRY PLUM SUGAR PEACH

6 MELON IN MY BELLY null null null

ID	COL1	COL2	COL3	COL4
1	THE BIG APPLE	ORANGE	PEAR	null
2	BANNANA EATS GRAPE	BLUEBERRY	null	null
3	THE BEST FRUIT IS WATERMELON	null	null	null
4	FRUITS	CANDY ARE THE BEST	WATER	null
5	APPLE STRAWBERRY	PLUM	SUGAR	PEACH
6	MELON IN MY BELLY	null	null	null

db<>fiddle here

CodePudding user response：

Replace every words to pipe | then split your string to array and write dynamic sql isnert