oracle字符串相关处理

1.逗号替换成'逗号'

replace(xxx,',',''',''') 

2.字符串两天拼接单引号

'''' || replace(xxx,',',''',''') || ''''

 

select '''' || replace(col_name ,',',''',''') || '''' as col_name2 from t_pur_process where id='dcbaa4feb67b443588b0f6c58b181b69'

3.创建split函数

CREATE OR REPLACE TYPE SPLIT_TABLE IS TABLE OF VARCHAR2 (4000)

CREATE OR REPLACE FUNCTION SPLIT(SPLIT_STRING VARCHAR2,SPLIT_DELIMITER VARCHAR2:=',')
RETURN SPLIT_TABLE
PIPELINED IS
IDX PLS_INTEGER;
V_STRING VARCHAR2(4000):=SPLIT_STRING;
BEGIN
LOOP
IDX:=INSTR(V_STRING,SPLIT_DELIMITER);
IF IDX>0 THEN
PIPE ROW(SUBSTR(V_STRING,1,IDX-1));
V_STRING:= SUBSTR(V_STRING,IDX + LENGTH(SPLIT_DELIMITER));
ELSE
PIPE ROW(V_STRING);
EXIT;
END IF;
END LOOP;
END;

split函数使用：SELECT COLUMN_VALUE AS SPLIT_STR FROM TABLE(SPLIT('张三,李四,王五', ','));

4.列数据转字符串

select LISTAGG(dept_name ,',') WITHIN GROUP (ORDER BY dept_name) from sys_dept ；