字符串转数组:
(SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <=
LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1)
数组转字符串
select wmsys.wm_concat(cityname) from sys_city
where provinceid='29'
SELECT M_ID, P, CV FROM (SELECT 1 M_ID , P_CUSTNO VAL FROM DUAL
WHERE P_CUSTNO IS NOT NULL)
MODEL
RETURN UPDATED ROWS
PARTITION BY(M_ID)
DIMENSION BY( 0 AS P)
MEASURES( VAL||',' AS CV)
RULES UPSERT
(CV
[ FOR P
FROM 1 TO LENGTH(REGEXP_REPLACE(CV[0],'[^'||','||']',NULL))
INCREMENT 1
] = RTRIM(REGEXP_SUBSTR( CV[0],'.*?'||',',1,CV(P)),','))
ORDER BY 1,2 ;
CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)
CREATE OR REPLACE FUNCTION split
(src VARCHAR2, delimiter varchar2)
RETURN mytable IS
psrc VARCHAR2(500);
a mytable := mytable();
i NUMBER := 1; --
j NUMBER := 1;
BEGIN
psrc := RTrim(LTrim(src, delimiter), delimiter);
LOOP
i := InStr(psrc, delimiter, j);
--Dbms_Output.put_line(i);
IF i>0 THEN
a.extend;
a(a.Count) := Trim(SubStr(psrc, j, i-j));
j := i+1;
--Dbms_Output.put_line(a(a.Count-1));
END IF;
EXIT WHEN i=0;
END LOOP;
IF j < Length(psrc) THEN
a.extend;
a(a.Count) := Trim(SubStr(psrc, j, Length(psrc)+1-j));
END IF;
RETURN a;
END;
/
SELECT * FROM student WHERE id IN (SELECT * FROM TABLE(CAST(split('001,002', ',')AS mytable)));
SELECT * FROM student WHERE id IN
(
SELECT id FROM student WHERE id='001'
UNION
SELECT * FROM TABLE(CAST(split('001,002',',') AS mytable))
);