Oracle sql with regex - getting duplicate records -

Question

here is my table records - table name is temp.

1   | java,c,.net
2   | oracle,hadoop,ruby

Actually i am looking for data like this.

1| java
1 | c
1 | .net
2 | oracle
2| hadoop
2 | ruby

I written below query and expected result is not matching. can you please check verify my query why it is causing deplicate,

select id,
regexp_substr(liked,'[^,]+', 1, level) from 
temp connect by regexp_substr(liked,'[^,]+', 1, level) is not null order by id

"Result is not matching" is not helpful. Please edit your question and include the results you're getting, along with the results you expected, and the query you used. You've got the last two, but you need to demonstrate in the question what the problem is. Providing an SQLFiddle which demonstrates the problem would be a good idea. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 17, 2018 at 13:12

David Faber · Accepted Answer · 2018-04-17 13:40:54Z

3

You want to do something like this:

 SELECT id, REGEXP_SUBSTR(liked, '[^,]+', 1, LEVEL)
   FROM temp 
CONNECT BY REGEXP_SUBSTR(liked, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL
  ORDER BY id;

This way using DISTINCT is not necessary.

EDIT: Without using a random number in the CONNECT BY clause, you will get an error as Oracle will think it is an infinite loop.

edited Apr 17, 2018 at 13:40

answered Apr 17, 2018 at 12:58

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

FDavidov Over a year ago

NOR it is fully safe. Random may (potentially) return the same result two consecutive times.

David Faber Over a year ago

Not sure what you mean by "fully safe" - care to explain?

David Faber Over a year ago

Use both columns in the CONNECT BY: AND PRIOR x = x AND PRIOR y = y

Learn Hadoop Over a year ago

Thanks @David Faber.. Working as expected

FDavidov Over a year ago

@DavidFaber, agree with the solution as SYS_GUID() is indeed UNIQUE in this case.

|

Bob Jarvis - Слава Україні · Accepted Answer · 2018-04-17 13:30:30Z

0

To get the exact result you're looking for you need to both use DISTINCT and you need to order by LEVEL within ID:

SELECT ID, LIKED
  FROM (select DISTINCT id, level, regexp_substr(liked,'[^,]+', 1, level) as liked
          from temp
          connect by regexp_substr(liked,'[^,]+', 1, level) is not null
          order by id, level);

SQLFiddle here

Best of luck.

answered Apr 17, 2018 at 13:30

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

1 Comment

Aldis Over a year ago

This is not the way to go. For already several records, it starts to heavily utilize the DB.

Collectives™ on Stack Overflow

Oracle sql with regex - getting duplicate records -

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related