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
12426 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 sql keyword?

1006

How to get unique records from a table?

975

Can cursors be part of a trigger body?

1641

What are different types of sql commands?

1118

Table 1: col1 Timestamp ---------------- 01-mar-2012 11:12:46 Table 2: col2 Timestamp -------------------- 01-mar-2012 11:12:10 01-mar-2012 11:11:23 Write a query to display a row with table2 col2 value less than tabl1 col1 value. Maximum timestamp value previous to table1 col1 value. Display a result as: Col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10

2113

What is a sql trace file?

1034

what is a database lock ? : Sql dba

1175

Explain foreign key in sql?

1035

What are pl/sql packages?

1107

Does sql require a server?

1019

how can we submit a form without a submit button? : Sql dba

995

Why trigger is used in sql?

976

What does varchar include?

953

hi,i plan to put experience on PLSQL ,can anyone suggest me for any institutes in bangalore or how to prepare for interviews

2017

how to select unique records from a table? : Sql dba

1115