oracle 空格转逗号sql,oracle split去逗号，行列转换 <转载> - sql server

最新推荐文章于 2025-01-25 11:22:39 发布

转载最新推荐文章于 2025-01-25 11:22:39 发布 · 296 阅读

文章标签：

本文详细介绍了在Oracle数据库中如何处理包含逗号分隔的字符串，包括使用内置函数`SYS.ODCIVARCHAR2LIST`和正则表达式`REGEXP_SUBSTR`进行拆分，以及自定义函数`fn_split`实现动态分隔。这些方法对于处理存储在单一字段中的多值数据非常有用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.针对 '1','2','3','4','5'(逗号在字符串外面)

[sql]

view plain copy

SQL>

SELECT

COLUMN_VALUE

FROM

TABLE

(SYS.ODCIVARCHAR2LIST(

'1'

'2'

'3'

'4'

'5'

));

COLUMN_VALUE

SQL> SELECT COLUMN_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));

COLUMN_VALUE

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

2.针对'1,2,3,4,5'(逗号在字符串里面)

[sql]

view plain copy

SQL>

select

regexp_substr(

'1,2,3,4,5'

'[^,]+'

,1,rownum)

from

dual

2 connect

rownum<=length(

'1,2,3,4,5'

)-length(

replace

(

'1,2,3,4,5'

','

))+1

3 ;

REGEXP_SUBSTR( '1,2,3,4,5'

,'[^,

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

SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual

2 connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1

3 ;

REGEXP_SUBSTR('1,2,3,4,5','[^,

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

3.使用函数

[sql]

view plain copy

CREATE

REPLACE

TYPE ty_str_split

TABLE

VARCHAR2 (4000);

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

[sql]

view plain copy

CREATE

REPLACE

FUNCTION

fn_split (p_str

CLOB, p_delimiter

VARCHAR2)

RETURN

ty_str_split

j INT

:= 0;

i INT

:= 1;

len INT

:= 0;

len1 INT

:= 0;

str VARCHAR2 (4000);

str_split ty_str_split := ty_str_split ();

BEGIN

len := LENGTH (p_str);

len1 := LENGTH (p_delimiter);

WHILE j < len

LOOP

j := INSTR (p_str, p_delimiter, i);

IF j = 0

THEN

j := len;

str := SUBSTR (p_str, i);

str_split.EXTEND;

str_split (str_split. COUNT

) := str;

IF i >= len

THEN

EXIT;

END

IF;

ELSE

str := SUBSTR (p_str, i, j - i);

i := j + len1;

str_split.EXTEND;

str_split (str_split. COUNT

) := str;

END

IF;

END

LOOP;

RETURN

str_split;

END

fn_split;

CREATE OR REPLACE FUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2)

RETURN ty_str_split

j INT := 0;

i INT := 1;

len INT := 0;

len1 INT := 0;

str VARCHAR2 (4000);

str_split ty_str_split := ty_str_split ();

BEGIN

len := LENGTH (p_str);

len1 := LENGTH (p_delimiter);

WHILE j < len

LOOP

j := INSTR (p_str, p_delimiter, i);

IF j = 0

THEN

j := len;

str := SUBSTR (p_str, i);

str_split.EXTEND;

str_split (str_split.COUNT) := str;

IF i >= len

THEN

EXIT;

END IF;

ELSE

str := SUBSTR (p_str, i, j - i);

i := j + len1;

str_split.EXTEND;

str_split (str_split.COUNT) := str;

END IF;

END LOOP;

RETURN str_split;

END fn_split;

测试：

[sql]

view plain copy

SQL>

select

from

table

(fn_split(

'1,2,3,4,5'

','

));

--第二个单引号中是前面字符串中需要被分隔的字符

COLUMN_VALUE

SQL> select

from

table

(fn_split(

'1,2,3,4。5'

'。'

));

COLUMN_VALUE

1,2,3,4

SQL>

SQL> select * from table(fn_split('1,2,3,4,5',',')); --第二个单引号中是前面字符串中需要被分隔的字符

COLUMN_VALUE -------------------------------------------------------------------------------- 1 2 3 4 5

SQL> select * from table(fn_split('1,2,3,4。5','。'));

COLUMN_VALUE -------------------------------------------------------------------------------- 1,2,3,4 5

SQL>