Splitting one column into multiple columns in Oracle

Let us see how we can use REGEX function for splitting one column values to multiple columns.
For example I have a table (i.e. named as SPLIT_COL) like below:

DESC SPLIT_COL
Name Null Type
---- ---- -------------
COL1 VARCHAR2(100)

Let us insert few records with pipe separators:

INSERT INTO SPLIT_COL (COL1) VALUES ('test|hello1');
INSERT INTO SPLIT_COL (COL1) VALUES ('test|hello2');
INSERT INTO SPLIT_COL (COL1) VALUES ('test|hello3');
INSERT INTO SPLIT_COL (COL1) VALUES ('test|hello4');
COMMIT;

So when we select the table it will look like :

SQL> select * from SPLIT_COL ;

COL1
------------------------------------------

test|hello1
test|hello2
test|hello3
test|hello4

Now if I want to separate the values like test into one column and whatever comes after pipe should be on another column then my select statement would look like:

SQL> SELECT REGEXP_SUBSTR(COL1,'[^|]+') COL1,
 REGEXP_SUBSTR(COL1,'[^|]+',1,2) COL2
from SPLIT_COL;

OUTPUT:


COL1 COL2
-------------------
test hello1
test hello2
test hello3
test hello4

Now if I have multiple values with pipe separated then I would add REGEX_SUBSTR with col2 ,col3 ….coln ..

Let us see how to do that. Insert one more record to SPLIT_COL table :

INSERT INTO SPLIT_COL (COL1) VALUES ('col1|col2|col3|col4|col5');
commit;

Now if I want to split this column values into 5 diffrent columns then my query would be like below:

SQL>SELECT REGEXP_SUBSTR(COL1,'[^|]+') COL1,
 REGEXP_SUBSTR(COL1,'[^|]+',1,2) COL2,
 REGEXP_SUBSTR(COL1,'[^|]+',1,3) COL3,
 REGEXP_SUBSTR(COL1,'[^|]+',1,4) COL4,
 REGEXP_SUBSTR(COL1,'[^|]+',1,5) COL5
from SPLIT_COL;

Result will be like below:
COL1 COL2 COL3 COL4 COL5
——————————————
test hello1
test hello2
test hello3
test hello4
col1 col2 col3 col4 col5

Advertisements

15 thoughts on “Splitting one column into multiple columns in Oracle

  1. Hi,

    I have a tables with below data
    Field1 | Field 2
    CAT1 |23,23,43,76,598,0,33,94,34,50,99,06.76,3s,adf,547,sdf
    CAT2 |hdsfd,dsf,dsfd,dsf,dsf,dfdds,ds,dsds,dsfds,dsf,ds,dsfds,ds

    I need the output in below format
    CAT1 |23,23
    CAT1|43,76
    CAT1|598,0
    CAT1|33,94 and so on for CAT 1
    CAT2|hdsfd,dsf

    Can anyone give me function for above output

    • You can try something like below:


      WITH q AS
      (SELECT 'CAT1' AS COL1,
      '23,23,43,76,598,0,33,94,34,50,99,06.76,3s,adf,547,sdf' as col2
      FROM dual
      )
      SELECT col1, TRIM( SUBSTR ( txt
      , INSTR (txt, ',', 2, level ) + 1
      , INSTR (txt, ',', 2, level+1
      )
      - INSTR (txt, ',', 1, level) -1 ) )
      AS u
      FROM ( SELECT col1, ','||col2||',' AS txt
      FROM q ---where where_clause

      )
      CONNECT BY level <=
      LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
      ;

      This is not the fully tested but you can try to do some fine tune to this 🙂 …

      Regards,
      Sloba

  2. Just a little added to my earlier reply :

    WITH q AS
    (SELECT ‘CAT1 |23,23,43,76,598,0,33,94,34,50,99,06.76,3s,adf,547,sdf’ AS COL1
    FROM dual
    )

    SELECT col1, TRIM( SUBSTR ( txt
    , INSTR (txt, ‘,’, 2, level ) + 1
    , INSTR (txt, ‘,’, 2, level+1
    )
    – INSTR (txt, ‘,’, 1, level) -1 ) )
    AS u
    FROM ( SELECT col1, ‘,’||col2||’,’ AS txt
    FROM (SELECT REGEXP_SUBSTR(COL1,'[^|]+’) COL1,
    REGEXP_SUBSTR(COL1,'[^|]+’,1,2) COL2
    from q) )
    CONNECT BY level <=
    LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
    ;

    Regards,
    Sloba

  3. Hi Sloba

    Thanks for your reply, My exact requirement is below,

    I’m having this data in a table and i want to create the below output. Can you please help me to achieve below output.

    CODE Values Max No of Element In Group
    Code-1 6419381,6964806,6957323,6855726,6710035,6697409,6629848,6514533 4
    Code2 5927380,5996637 2
    Code 3 5903238,5903315 3
    Order Code. 5089608,6236854,6227583,6224093,6179370,6085658 2
    Cancelled Code 6160535,6201385 2
    Closed Code 5336661,5783054,5671026 2
    Entered Code 6127022,6244437,6242787,6240948,6236972,6195144,6174198 4

    For Code 1 Max No of Output is 4, for Code 2 its 2 and so on
    Below is required output
    CODE Values
    Code-1 6419381,6964806,6957323,6855726
    Code-1 6710035,6697409,6629848,6514533
    Code2 5927380,5996637
    Order Code 5089608,6236854
    Order Code 6227583,6224093
    Order Code 6179370,6085658
    Cancelled Code 6160535,6201385
    Closed Code 5336661,5783054
    Entered Code 6127022,6244437,6242787,6240948

    • Hi Manish,

      Assuming your table as
      CREATE TABLE code_tab(
      CODE VARCHAR2(100),
      L_VALUES VARCHAR2(4000),
      GROUP_SIZE NUMBER);

      And inserting the data as provided,

      BEGIN
      insert into CODE_TAB values( ‘Code-1’, ‘6419381,6964806,6957323,6855726,6710035,6697409,6629848,6514533’, 4);
      insert into CODE_TAB values( ‘Code-2’, ‘5089608,6236854,6227583,6224093’, 2);
      commit;
      END;

      You can achieve your desired result with:

      SELECT code
      ,regexp_substr(l_values
      ,'(([^,]+,){0,’ || (group_size – 1) || ‘}[^,]+)’
      ,1
      ,rn) AS l_values_split
      ,group_size
      FROM code_tab
      CROSS JOIN (SELECT rownum rn
      FROM (SELECT ceil(MAX(regexp_count(l_values
      ,’,’) / group_size) + 1) mx
      FROM code_tab)
      CONNECT BY LEVEL <= mx)
      WHERE regexp_substr(l_values
      ,'(([^,]+,){0,' || (group_size – 1) || '}[^,]+)'
      ,1
      ,rn) IS NOT NULL
      ORDER BY code
      ,rn;

      • Hi

        Can you please send me the above code in email, I’m getting error while executing the code.

        Regards
        Manish

  4. SELECT code,
    REGEXP_SUBSTR (l_values,
    ‘(([^,]+,){0,’ || (group_size – 1) || ‘}[^,]+)’,
    1,
    rn)
    AS l_values_split,
    group_size
    FROM code_tab
    CROSS JOIN
    ( SELECT ROWNUM rn
    FROM (SELECT CEIL (
    MAX (REGEXP_COUNT (l_values, ‘,’) / group_size)
    + 1)
    mx
    FROM code_tab)
    CONNECT BY LEVEL <= mx)
    WHERE REGEXP_SUBSTR (l_values,
    '( ([^,]+,){0,' || (group_size – 1) || '}[^,]+)',
    1,
    rn)
    IS NOT NULL
    ORDER BY code, rn

    I'm executing this code but i'm not getting any records as output;

    • Hi Manish,
      What is the table structure I am not sure neither Keron knows it.
      The example was provided to get the expected result that you are looking for. I am assuming that you might be having one SQL which would give you the code, values and max group number.

      Let us take an example assuming I created one view based on your SQL. To get this let us create a table :

      create table CODE_TAB
      (
      CODE VARCHAR2(100)
      ,L_VALUES VARCHAR2(4000)
      ,GROUP_SIZE NUMBER
      ) ;

      Let us know insert the sample data which you might get from the first SQL :

      begin
      insert into CODE_TAB values( ‘Code-1’, ‘6419381,6964806,6957323,6855726,6710035,6697409,6629848,6514533’, 4);
      insert into CODE_TAB values( ‘Code-2’, ‘5089608,6236854,6227583,6224093’, 2);
      commit;

      Based on the data the expected result would be:

      CODE Values
      ———————————————————————-
      Code-1 6419381,6964806,6957323,6855726
      Code-1 6710035,6697409,6629848,6514533
      Code2 5089608,6236854
      Code2 6227583,6224093

      Let us run the below SQL :

      SELECT code, regexp_substr(l_values ,'(([^,]+,){0,’||(group_size-1)||’}[^,]+)’,1,rn) AS l_values_split, l_values AS l_values_orig, group_size
      FROM code_tab
      CROSS JOIN (SELECT rownum rn
      FROM (SELECT ceil(MAX(regexp_count(l_values,’,’)/group_size)+1) mx
      FROM code_tab)
      CONNECT BY LEVEL <= mx)
      WHERE regexp_substr(l_values ,'(([^,]+,){0,'||(group_size-1)||'}[^,]+)',1,rn) IS NOT NULL
      ORDER BY code, rn ;

      This will give you the result you expected.

  5. I have a table called T1 and Column C1

    T1
    ======

    C1
    —-

    a,b,c,d
    d,e,f,g
    g,h,i,j
    k,l,m,n

    and I have to get a result as

    C1
    —–
    a
    b
    c
    d
    e
    f
    g
    h
    i
    j
    k
    l
    m
    n

    Can anyone plz help me out to get a solution for this one?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s