fn_split('${v_jsr}', '#')作用:把字符串中的#号去掉:
CREATE OR REPLACE FUNCTION fn_split
(
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN ty_str_split IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2(32767);
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;
Oracle语法instr和substr:
instr
SELECT INSTR('CORPORATE FLOOR', 'OR', 0, 1) FROM DUAL; 返回值为0
SELECT INSTR('CORPORATE FLOOR', 'OR', 2, 1) FROM DUAL; 返回值为2
SELECT INSTR('CORPORATE FLOOR', 'OR', 2, 2) FROM DUAL; 返回值为5
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) FROM DUAL; 返回值为14
SELECT INSTR('CORPORATE FLOOR', 'OR', -5, 1) FROM DUAL; 返回值为5
substr
SELECT SUBSTR('This is a test', 0, 2) value from dual; 返回值Th
SELECT SUBSTR('This is a test', 1, 2) value from dual; 返回值Hi
SELECT SUBSTR('This is a test', -1, 2) value from dual; 返回值t
SELECT SUBSTR('This is a test', -2, 2) value from dual; 返回值st