oracle中逗号分割,Oracle-分割字符串逗号分隔（字符串包含空格和连续逗号）

使用MODEL子句：

WITH sample_data AS (

SELECT '12 3,456,,,,,abc,def' str FROM dual

)

-- end of sample_data mimicking real table

,

model_param AS (

SELECT str AS orig_str ,

','

|| str

|| ',' AS mod_str ,

1 AS start_pos ,

Length(str) AS end_pos ,

(LENGTH(str) -

LENGTH(REPLACE(str, ','))) + 1 AS element_count ,

0 AS element_no ,

ROWNUM AS rn

FROM sample_data )

SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str

FROM (

SELECT *

FROM model_param

MODEL PARTITION BY ( rn, orig_str, mod_str)

DIMENSION BY (element_no)

MEASURES (start_pos, end_pos, element_count)

RULES ITERATE (2000)

UNTIL (ITERATION_NUMBER+1 = element_count[0])

( start_pos[ITERATION_NUMBER+1] =

instr(cv(mod_str), ',', 1, cv(element_no)) + 1,

end_pos[ITERATION_NUMBER+1] =

instr(cv(mod_str), ',', 1, cv(element_no) + 1) )

)

WHERE element_no != 0

ORDER BY mod_str ,

element_no

/

输出强>

STR

----------------------

12 3

456

abc

def

8 rows selected.

如果你想在PL / SQL中进行，那么你可以使用流水线表函数：

SQL> CREATE OR REPLACE TYPE test_type

2 AS

3 TABLE OF VARCHAR2(100)

4 /

Type created.

SQL> CREATE OR REPLACE FUNCTION comma_to_table(

2 p_list IN VARCHAR2)

3 RETURN test_type PIPELINED

4 AS

5 l_string LONG := p_list || ',';

6 l_comma_index PLS_INTEGER;

7 l_index PLS_INTEGER := 1;

8 BEGIN

9 LOOP

10 l_comma_index := INSTR(l_string, ',', l_index);

11 EXIT

12 WHEN l_comma_index = 0;

13 PIPE ROW ( TRIM(SUBSTR(l_string, l_index, l_comma_index - l_index)));

14 l_index := l_comma_index + 1;

15 END LOOP;

16 RETURN;

17 END comma_to_table;

18 /

Function created.

让我们看一下输出：

SQL> SELECT *

2 FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))

3 /

COLUMN_VALUE

------------------------------------------------------------------------------

12 3

456

abc

def

8 rows selected.

SQL>