表test中,有一个大字段data1,里面的数据是这个样子:
01,02,03,04 而01到04又是另外一个字段的代码值,通过select查询,把这些代码值翻译过来,这个函数怎么写呢?以前没有写过这样的,又不知道语法,求指教
01,02,03,04 而01到04又是另外一个字段的代码值,通过select查询,把这些代码值翻译过来,这个函数怎么写呢?以前没有写过这样的,又不知道语法,求指教
------------------------------
01,02,03,04SQL> select * from a3;COL1 COL2
---------- ----------
03 cc
04 cc
01 aa
02 bbSQL> select tid, col1, col2
2 from a3,
3 (select distinct regexp_substr(id, '[^,]+', 1, level) tid from a2
4 connect by level <= length(id) - length(replace(id, ',', '')) + 1)
5 where tid = col1;TID COL1 COL2
---------- ---------- ----------
03 03 cc
04 04 cc
01 01 aa
02 02 bb
ID 01,02,03,04
IDvalue 01值, 02值, 03值, 04值
还是以逗号隔开,只是把代码替换成值了
CREATE OR REPLACE FUNCTION split(p_str IN VARCHAR2,
p_delimiter IN VARCHAR2)
RETURN tbl_split IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2(4000);
str_split tbl_split := tbl_split();
BEGIN
IF p_delimiter is not null THEN
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;
ELSE
len := LENGTH(p_str);
j := 1;
WHILE (p_str is not null and j <= len ) LOOP
str := substrb(p_str, j, 1);
str_split.EXTEND;
str_split(str_split.COUNT) := str;
j := j + 1;
/*
IF j >= len THEN
EXIT;
END IF;
*/
END LOOP;
END IF;
RETURN str_split;
END split;现在的提供一个给你。
------
1 01
2 02
3 03
4 04
SELECT wm_concat(REGEXP_SUBSTR('01,02,03,04', '[^,]+', 1, N)||'值') COL1 FROM DUAL,
(SELECT ROWNUM N FROM DUAL
CONNECT BY ROWNUM < LENGTH(REGEXP_REPLACE('01,02,03,04', '[^,]', NULL)) + 2)
WHERE N <= LENGTH(REGEXP_REPLACE('01,02,03,04', '[^,]', NULL)) + 1;
COL1
--------------------------------------------------------------------------------
01值,02值,03值,04值