How to split the columns?

Question

Name            Nameid
P,q,r,s,t       One
A,b,c           Two
D,e             Three

This is my source table, but i want my target table like this

Name    Nameid
P       One
Q       One
R       One
S       One
T       One
A       Two
B       Two
C       Two
D       three

possible duplicate of [Split column to multiple rows](http://stackoverflow.com/questions/3713107/split-column-to-multiple-rows) — APC, Mar 02 '12 at 13:25

score 1 · Answer 1 · answered Mar 02 '12 at 13:16

1

In a case like this, I think it would be more elegant to store the data in a different way.

If you are inserting your rows from a program, try splitting your string there, and insert a few more rows instead.

Let me give you a pseudo code example.

number = "One"
many_letters = "P,Q,R,S,T".split(",")
for(letter in many_letters) {
     insert_values(letter, number)
}

answered Mar 02 '12 at 13:16

professorsloth

339
1
10

I'm not sure this is what he asked. The question is vague though. – vulkanino Mar 02 '12 at 13:20
You're right. I'm thinking it is a potential solution, though. – professorsloth Mar 02 '12 at 13:23

score 1 · Answer 2 · answered Mar 02 '12 at 15:14

Here's one way, lifted from here:

SQL> CREATE TABLE t (name VARCHAR2(20), nameid VARCHAR2(10));

Table created.

SQL> INSERT INTO t VALUES ('P,q,r,s,t','One');

1 row created.

SQL> INSERT INTO t VALUES ('A,b,c'    ,'Two');

1 row created.

SQL> INSERT INTO t VALUES ('D,e'      ,'Three');

1 row created.

SQL> SELECT     nameid
  2  ,          REGEXP_SUBSTR (name, '[^,]+', 1, LEVEL) AS token
  3  FROM       t
  4  CONNECT BY PRIOR nameid = nameid
  5  AND        REGEXP_INSTR (name, '[^,]+', 1, LEVEL) > 0
  6  AND        PRIOR DBMS_RANDOM.STRING ('p', 10) IS NOT NULL
  7  ;

NAMEID     TOKEN
---------- --------------------
One        P
One        q
One        r
One        s
One        t
Three      D
Three      e
Two        A
Two        b
Two        c

10 rows selected.

SQL>

Tobsey · Answer 3 · 2012-03-02T13:37:27.083

Sorry I didn't understand the question it was posted. It's been reformatted since.

Oracle does not have a built in Split function, which is what you need here. The following link shows how to create a custom Split function:

http://srinisreeramoju.blogspot.com/2010/03/oracle-custom-split-function.html

Once you've created the types and the function you would simply call it as:

SELECT
    Split(Name, ',') AS Name,
    NameID
FROM
    YourTable

How to split the columns?

3 Answers3