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.
Answers were Sorted based on User's Feedback
Answer / kavitha nedigunta
create table test001 (cuss varchar2(10), acc varchar2(30));
insert into test001 values ('a','1');
insert into test001 values ('b','2|3');
insert into test001 values ('c','4|5|6');
WITH CTE AS (SELECT CUSS,ACC FROM TEST001)
select distinct trim(regexp_substr( acc, '[^|]+', 1,
level)) acc ,cuss from cte
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ACC, '[^|]+')) +
1;
Is This Answer Correct ? | 3 Yes | 3 No |
Answer / p deshmukh
SELECT distinct COL, trim(regexp_substr(colvalue, '[^|]+', 1, level)) colvalue
FROM (with demo as ( select '1' as a, '2|3' as b, '4|5|6' as c from dual )
select * from demo
unpivot
( colvalue for col in (a, b, c) )) t
CONNECT BY instr(colvalue, '|', 1, level - 1) > 0
order by col
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / 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 |
How to find the count of letter "L" in HELLO
How to display the records between two range in Oracle SQL Plus?
How do I find duplicates in sql?
Which one is faster ienumerable or iqueryable?
What is identity column in sql server?
How can I see all tables in sql?
What are the different types of functions in sql?
What is join view in sql?
What is raw datatype in sql?
how can we find the number of rows in a table using mysql? : Sql dba
Explain ttitle and btitle.
Can we create table in function?