Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT*FROM Split('A,B,C,D',',')
- |Id | Data
- -- ----
- |1 | A |
- |2 | B |
- |3 | C |
- |4 | D |
- SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
- FROM dual
- CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE TYPE T_TABLE IS OBJECT
- (
- Field1 int
- , Field2 VARCHAR(25)
- );
- CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
- /
- CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
- RETURN T_TABLE_COLL
- IS
- l_res_coll T_TABLE_COLL;
- l_index number;
- BEGIN
- l_res_coll := T_TABLE_COLL();
- FOR i IN (
- WITH TAB AS
- (SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
- UNION
- SELECT '1002' ID, 'D,E,F' STR FROM DUAL
- UNION
- SELECT '1003' ID, 'C,E,G' STR FROM DUAL
- )
- SELECT id,
- SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
- FROM
- ( SELECT ',' || STR || ',' AS STR, id FROM TAB
- ),
- ( SELECT level AS lvl FROM dual CONNECT BY level <= 100
- )
- WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
- ORDER BY ID, NAME)
- LOOP
- IF i.ID = 1001 THEN
- l_res_coll.extend;
- l_index := l_res_coll.count;
- l_res_coll(l_index):= T_TABLE(i.ID, i.name);
- END IF;
- END LOOP;
- RETURN l_res_coll;
- END;
- /
- select * from table(TEST_RETURN_TABLE());
- SQL> select * from table(TEST_RETURN_TABLE());
- FIELD1 FIELD2
- ---------- -------------------------
- 1001 A
- 1001 B
- 1001 C
- 1001 D
- 1001 E
- 1001 F
- 6 rows selected.
- CREATE OR REPLACE FUNCTION split_String(
- i_str IN VARCHAR2,
- i_delim IN VARCHAR2 DEFAULT ','
- ) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
- AS
- p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
- p_start NUMBER(5) := 1;
- p_end NUMBER(5);
- c_len CONSTANT NUMBER(5) := LENGTH( i_str );
- c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
- BEGIN
- IF c_len > 0 THEN
- p_end := INSTR( i_str, i_delim, p_start );
- WHILE p_end > 0 LOOP
- p_result.EXTEND;
- p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
- p_start := p_end + c_ld;
- p_end := INSTR( i_str, i_delim, p_start );
- END LOOP;
- IF p_start <= c_len + 1 THEN
- p_result.EXTEND;
- p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
- END IF;
- END IF;
- RETURN p_result;
- END;
- /
- SELECT ROWNUM AS ID,
- COLUMN_VALUE AS Data
- FROM TABLE( split_String( 'A,B,C,D' ) );
- ID DATA
- -- ----
- 1 A
- 2 B
- 3 C
- 4 D
- CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
- v_res sys_refcursor;
- begin
- open v_res for
- WITH TAB AS
- (SELECT p_str STR FROM DUAL)
- select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
- from
- ( select ',' || STR || ',' as STR from TAB ),
- ( select level as lvl from dual connect by level <= 100 )
- where lvl <= length(STR) - length(replace(STR, ',')) - 1;
- return v_res;
- end;
- CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
- v_emptype t_empty_type := t_empty_type();
- v_cnt number := 0;
- v_res sys_refcursor;
- v_value nvarchar2(128);
- begin
- open v_res for
- WITH TAB AS
- (SELECT p_str STR FROM DUAL)
- select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
- from
- ( select ',' || STR || ',' as STR from TAB ),
- ( select level as lvl from dual connect by level <= 100 )
- where lvl <= length(STR) - length(replace(STR, ',')) - 1;
- loop
- fetch v_res into v_value;
- exit when v_res%NOTFOUND;
- v_emptype.extend;
- v_cnt := v_cnt + 1;
- v_emptype(v_cnt) := empty_type(v_value);
- end loop;
- close v_res;
- return v_emptype;
- end;
- SELECT * FROM (TABLE(split('a,b,c,d,g')))
- create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
- RETURN VARCHAR2
- IS
- NTH_SUBSTR VARCHAR2(500);
- POS1 NUMBER(4);
- POS2 NUMBER(4);
- BEGIN
- IF NTH_PART=1 THEN
- SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1) INTO POS1 FROM DUAL;
- SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
- ELSE
- SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO POS1 FROM DUAL;
- SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART) INTO POS2 FROM DUAL;
- SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
- END IF;
- RETURN NTH_SUBSTR;
- END;
- SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
- WHILE NO_OF_RECORDS>0
- LOOP
- PK_RECORD :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
- -- do some thing
- NO_OF_RECORDS :=NO_OF_RECORDS-1;
- END LOOP;
- select
- split.field(column_name,1,',','"') name1,
- split.field(column_name,2,',','"') name2
- from table_name
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement