SQL - Divide single column in multiple columns

oracle, select, split, sql

Solution

Depends on the consistency of the data - assuming a single space is the separator between what you want to appear in column one vs two:

WITH TEST_DATA AS
  (SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)

SELECT SUBSTR(t.COLUMN_A, 1, INSTR(t.COLUMN_A, ' ')-1) AS COLUMN_A,
       SUBSTR(t.COLUMN_A, INSTR(t.COLUMN_A, ' ')+1) AS COLUMN_B
FROM test_data T;

You can also use below query with REGEX:

WITH TEST_DATA AS
   (SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)

SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
       REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B
FROM test_data T;

Oracle 10g+ has regex support, allowing more flexibility depending on the situation you need to solve. It also has a regex substring method...

EDIT: 3 WORDS SPLIT:

WITH TEST_DATA AS
  (SELECT 'LOREM IPSUM DIMSUM' COLUMN_A FROM DUAL)

 SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
     REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B,
     REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 2, 3) COLUMN_C
 FROM test_data T;

Reference:

- SUBSTR

- INSTR

Problem

I have the following SQL question: How to divide a column (text inside) using the SELECT command into two separate columns with split text? I need to separate the text-data, using the space character. I know it is better to put an example to make it easy. So: ``` SELECT COLUMN_A FROM TABLE1 ``` output: ``` COLUMN_A ----------- LORE IPSUM ``` desired output: ``` COLUMN_A COLUMN_B --------- ---------- LORE IPSUM ``` Thank you all for the help.

Original source