Split Varchar into Separate Columns in Oracle

Split varchar into separate columns in Oracle

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:

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
       SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two
  FROM YOUR_TABLE t

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

Reference:

• SUBSTR
• INSTR

Separate comma separated string into columns oracle sql

You can use REGEXP_SUBSTR, but you must specify the number of columns.

SELECT agentname
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 1)    AS CATA
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 2)    AS CATB  
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 3)    AS CATC 
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 4)    AS CATD
FROM commasplit;

demo in db<>fiddle

Split comma separated values to columns in Oracle

You can use regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1, 
       regexp_substr(val, '[^,]+', 1, 2) as val2, 
       regexp_substr(val, '[^,]+', 1, 3) as val3, 
       . . .

I would suggest that you generate a column of 255 numbers in Excel (or another spreadsheet), and use the spreadsheet to generate the SQL code.

oracle sql split text into columns based on each occurrence of a certain character set

You can just remove the sub-strings which do not have the correct prefix:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEXT_RECORDS (
    CONVERSATION CLOB
    );

INSERT INTO TEXT_RECORDS(CONVERSATION)
SELECT 'a:some_text_1 c:some_text_2 a:some_text_3 c:some_text_4 a:some_text_5 c:some_text_6' FROM DUAL UNION ALL
SELECT 'a:some_text_1 a:some_text_2 a:some_text_3' FROM DUAL UNION ALL
SELECT 'c:some_text_1 a:some_text_2 a:some_text_3 c:some_text_4' FROM DUAL;

Query 1:

SELECT REGEXP_REPLACE(
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             conversation,
             '.*?(a:(\S+))?(\s|$)',  -- Find each word starting with "a:"
             '\2, '                  -- replace with just that part without prefix
           ),
           '(, ){2,}', -- Replace multiple delimiters
           ', '        -- With a single delimiter
         ),
         '^, |, $'     -- Remove leading and trailing delimiters
       ) AS conv_agent,
       REGEXP_REPLACE(
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             conversation,
             '.*?(c:(\S+))?(\s|$)',  -- Find each word starting with "c:"
             '\2, '                  -- replace with just that part without prefix
           ),
           '(, ){2,}', -- Replace multiple delimiters
           ', '        -- With a single delimiter
         ),
         '^, |, $'     -- Remove leading and trailing delimiters
       ) AS conv_customer
FROM   text_records

Results:

|                            CONV_AGENT |                         CONV_CUSTOMER |
|---------------------------------------|---------------------------------------|
| some_text_1, some_text_3, some_text_5 | some_text_2, some_text_4, some_text_6 |
| some_text_1, some_text_2, some_text_3 |                                       |
|              some_text_2, some_text_3 |              some_text_1, some_text_4 |

Updated - Spaces in conversation sentences

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEXT_RECORDS (
    CONVERSATION CLOB
    );

INSERT INTO TEXT_RECORDS(CONVERSATION)
SELECT 'a:some text 1 c:some text 2 a:some text 3 c:some text 4 a:some text 5 c:some text 6' FROM DUAL UNION ALL
SELECT 'a:some text 1 a:some text 2 a:some text 3' FROM DUAL UNION ALL
SELECT 'c:some text 1 a:some text 2 a:some text 3 c:some text 4' FROM DUAL;

Query 1:

SELECT REGEXP_REPLACE(
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             conversation,
             '.*?(a:([^:]*))?(\s|$)',
             '\2, '
           ),
           '(, ){2,}',
           ', '
         ),
         '^, |, $'
       ) AS conv_agent,
       REGEXP_REPLACE(
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             conversation,
             '.*?(c:([^:]*))?(\s|$)',
             '\2, '
           ),
           '(, ){2,}',
           ', '
         ),
         '^, |, $'
       ) AS conv_customer
FROM   text_records

Results:

|                            CONV_AGENT |                         CONV_CUSTOMER |
|---------------------------------------|---------------------------------------|
| some text 1, some text 3, some text 5 | some text 2, some text 4, some text 6 |
| some text 1, some text 2, some text 3 |                                       |
|              some text 2, some text 3 |              some text 1, some text 4 |

Oracle split string and update in new column

This task is not complex enough to require a regex. Simple string functions can be used, and should probably be preferred, since they are less expensive:

update mytable
set 
    first_name = substr(first_name, 1, instr(first_name, ' ') - 1),
    last_name  = substr(first_name, instr(first_name, ' ') + 1)
where instr(first_name, ' ') > 0

split a string in oracle into multiple columns based on a delimiter

Here's an example.

Sample data is in lines #1 - 5 (you already have that in your table, don't type that); query that does the job begins at line #6.

SQL> with test (order_no, datum, service_cd) as
  2    (select 17, date '2016-11-30', '2106|2100|2105'      from dual union all
  3     select 23, date '2016-11-30', '2043|2020|2023|2047' from dual union all
  4     select 67, date '2016-11-30',  null                 from dual
  5    )
  6  select order_no,
  7         datum,
  8         regexp_substr(service_cd, '[^|]+', 1, column_value) val
  9  from test cross join
 10    table(cast(multiset(select level from dual
 11                        connect by level <= regexp_count(service_cd, '\|') + 1
 12                       ) as sys.odcinumberlist))
 13  order by order_no, datum, column_value;

  ORDER_NO DATUM      VAL
---------- ---------- ----------
        17 30/11/2016 2106
        17 30/11/2016 2100
        17 30/11/2016 2105
        23 30/11/2016 2043
        23 30/11/2016 2020
        23 30/11/2016 2023
        23 30/11/2016 2047
        67 30/11/2016

8 rows selected.

SQL>

Oracle SQL Select a Variable and split it by semicolon

OK, you have semi-colon separated list of values. You said that you want to have them in different rows, but - that's not what example shows ... this:

I would like it to look like this when using Select:

test test1 test2 test3

is only one row, with space as a separator.

---

Anyway: presuming that you really want different rows, then replace current separator with a line feed character (chr(10)), e.g.

SQL> select replace('test;test1;test2;test3', ';', chr(10)) result
  2  from dual;

RESULT
----------------------
test
test1
test2
test3

SQL>

---

As it turns out you need different columns after all, then - with such a sample data - regular expressions are a simple solution:

SQL> with test (col) as
  2    (select 'test;test1;test2;test3' from dual)
  3  select regexp_substr(col, '\w+', 1, 1) col1,
  4         regexp_substr(col, '\w+', 1, 2) col2,
  5         regexp_substr(col, '\w+', 1, 3) col3,
  6         regexp_substr(col, '\w+', 1, 4) col4
  7  from test;

COL1 COL2  COL3  COL4
---- ----- ----- -----
test test1 test2 test3

SQL>

---

Related Topics