SELECT a, REGEXP_SUBSTR (b, '([^|]+)', 1, lvl) b FROM dummy

I have a tablle like this.
cust acc
---------------
a 1
b 2|3
c 4|5|6

I Want below o/p:
cust acc
-----------
a 1
b 2
b 3
c 4
c 5
c 6
Please any one can you have any ideas share me.
I have urgent requirement.

Question Posted / punith

4 Answers
12614 Views
Cap Gemini, MTS, I also Faced
E-Mail Answers

Answer Posted / punith

SELECT a,
REGEXP_SUBSTR (b, '([^|]+)', 1, lvl) b

FROM dummy,
(SELECT LEVEL lvl
FROM (SELECT MAX (LENGTH (REGEXP_REPLACE
(b, '[^|]'))) mx
FROM dummy)
CONNECT BY LEVEL <= mx + 1)
WHERE lvl - 1 <= LENGTH (REGEXP_REPLACE (b, '[^|]'));

Is This Answer Correct ?

0 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is rename in sql?

1018

How do sql databases work?

1045

What is a string data type in sql?

1101

What is string join?

1025

How many triggers can be applied on a table?

988

Where is pl sql used?

1005

What is an index in sql with example?

1021

Are null values same as that of zero or a blank space?

1069

Can we use ddl statements in stored procedure sql server?

1063

How do you write a subquery?

1070

How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?

1177

Advantages and disadvantages of stored procedure?

1167

Explain the significance of the & and && operators in pl sql.

1048

List different type of expressions with the example.

973

Does access use sql?

1101