There is a table, save data is probably:
ID DESC
1 A, B, C and DE
2 M, LD, D, C
3 H, MM, SD, WE
How to split into:
ID DESC
1 A
1 B
1 C
DE 1
2 M
2 LD
2 D
2 C
3 H
3 MM
3 SD
3 WE
Online information are mostly regexp_substr plus connect by implementing a single field, but here is the result of after break up with the corresponding ID Numbers, because the correlation ID is required to obtain corresponding multiline results after break up,
Beg god grant instruction!

CodePudding user response:

And id=the prior id

CodePudding user response:

Select id, regexp_substr (des, '[^,] +' 1, level) new_des
The from desc_info
Connect by level & lt;=regexp_count (des, ', ') + 1
And the prior rowid=rowid
And the prior dbms_random. The value is not null;
Results:
1 A
1 B
1 C
DE 1
2 M
2 LD
2 D
2 C
3 H
3 MM
3 SD
3 WE

CodePudding user response:

refer to the second floor wangfutai91 response:

select id, regexp_substr (des, '[^,] +, 1, level) new_des
The from desc_info
Connect by level & lt;=regexp_count (des, ', ') + 1
And the prior rowid=rowid
And the prior dbms_random. The value is not null;
Results:
1 A
1 B
1 C
DE 1
2 M
2 LD
2 D
2 C
3 H
3 MM
3 SD
3 WE

I want to check id=1 data split results,
Id desc
[align=left] 1 A
1 B
1 C

1 DE [/align]
How to write?

CodePudding user response:

With TMP as
(select id, regexp_substr (des, '[^,] +' 1, level) new_des
The from desc_info
Connect by level & lt;=regexp_count (des, ', ') + 1
And the prior rowid=rowid
And the prior dbms_random. The value is not null)
Select * from TMP where id=1;


1 A
1 B
1 C
DE 1
This: [align=left] what is not clear

CodePudding user response:

reference 4 floor wangfutai91 response:

with TMP as
(select id, regexp_substr (des, '[^,] +' 1, level) new_des
The from desc_info
Connect by level & lt;=regexp_count (des, ', ') + 1
And the prior rowid=rowid
And the prior dbms_random. The value is not null)
Select * from TMP where id=1;


1 A
1 B
1 C
DE 1
This: [align=left] don't know what is

Desc_info table has a large amount of data, the query efficiency is very poor

CodePudding user response:

reference 5 floor xibmg reply:

Quote: refer to 4th floor wangfutai91 response:

with TMP as
(select id, regexp_substr (des, '[^,] +' 1, level) new_des
The from desc_info
Connect by level & lt;=regexp_count (des, ', ') + 1
And the prior rowid=rowid
And the prior dbms_random. The value is not null)
Select * from TMP where id=1;


1 A
1 B
1 C
DE 1
This: [align=left] don't know what is

Desc_info table has a large amount of data, the query efficiency is very poor

This is certainly recursive performance is poor, and he is to put all the results generated by the cet table to select,
You can add the where id=1 in split character directly on the SQL query,