Returns the result set of the ORACLE character split function.

ORACLE does not support direct return of table type like MSSQL. Therefore, you must first create a custom type. The nested table is used here ). 1234567891011121314151617181920212223242526272829303132333435363738394041 -- NestedTableC

ORACLE does not support direct return of table type like MSSQL. Therefore, you must first create a custom type. This statement uses a Nested Table ). 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41 -- Nested Table C

ORACLE does not support direct return of table type like MSSQL. Therefore, you must first create a custom type. This statement uses a Nested Table ).

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

-- Nested Table

Create or replace type split_str is table of varchar (100 );

/

-- Function

Create or replace function fn_Split

(

P_Str VARCHAR2,

P_Delimiter VARCHAR2

)

RETURN split_str PIPELINED

AS

V_Str VARCHAR (4000): = p_Str;

V_Index NUMBER;

V_SubLength NUMBER;

BEGIN

-- P_Delimiter is null

IF p_Delimiter IS NULL THEN

FOR x IN 1 .. LENGTH (v_Str)

LOOP

Pipe row (SUBSTR (V_Str, x, 1 ));

End loop;

RETURN;

End if;

V_index: = INSTR (v_Str, p_Delimiter );

WHILE v_Index <> 0

LOOP

Pipe row (SUBSTR (v_Str, 1, v_Index-1 ));

V_SubLength: = LENGTH (v_Str)-(v_index + LENGTH (p_Delimiter)-1 );

V_Str: = SUBSTR (v_Str,-v_SubLength, v_SubLength );

V_index: = INSTR (v_Str, p_Delimiter );

End loop;

Pipe row (v_Str );

RETURN;

END;

/

-- Using (the second method is better)

SELECT fn_split ('this @ is @ it', '@') FROM dual;

SELECT * FROM Table (fn_split ('this @ is @ it ','@@'));