Implementation of comma-separated column-to-row in Oracle

Now, if a permission number is provided and you want to retrieve the user set with the permission, you need to match the given permission number among multiple permission numbers separated by commas. If you use like, one is less efficient, and the other is inaccurate. Therefore, the comma-separated column-to-row method is used. Currently, this method is only applicable to oracle databases. This method only requires SQL statements to implement column-to-row conversion.

The following is an example of this method:

Select a, B, c from
(With test as (select 'aaa' a, 'bbb 'B, '1, 2,3 'C from dual)
Select a, B, substr (t. ca, instr (t. ca, ',', 1, c. lv) + 1, instr (t. ca, ',', 1, c. lv + 1)-(instr (t. ca, ',', 1, c. lv) + 1) AS c
From (select a, B, ',' | c | ', 'as ca, length (c |', ')-nvl (length (REPLACE (c, ','), 0) AS cnt FROM test) t,
(Select LEVEL lv from dual connect by level <= 100) c where c. lv <= t. cnt)

Run the preceding code. The result is as follows:

The content "1, 2, 3" in the simulation data column c is converted into three rows, and the content of the first two columns is included in the past. In actual use, you only need

Select 'aaa' a, 'bbb 'B, '1, 2, 3' c from dual to replace the fields in the data table to be converted, the c field must be the field for saving the comma-separated content for conversion. The following content does not need to be changed. The number 100 in "LEVEL <= 100" indicates the number of times that a comma appears in the content of the matching field. You can change the value on your own.