Converts column data separated by commas (,) in a column into a function for displaying row data.

Converts column data separated by commas (,) in a column into a function for displaying row data.

<Pre name = "code" class = "SQL"> This is an example of a string segmentation function. select to_number (strvalue) as Value from table (fn_split ('1, 2, 3 ', ',') select to_char (strvalue) as Value from table (fn_split ('aa, bb, CC ',','))

</Pre> <pre name = "code" class = "SQL"> function:

</Pre> <pre name = "code" class = "SQL"> CREATE OR REPLACE FUNCTION ROOT. fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_tbl_str_split IS j INT: = 0; I INT: = 1; -- the length of the split source string. len INT: = 0; -- length of the separator string len1 INT: = 0; -- text information of each cell in the middle of the temporary storage. str VARCHAR2 (4000); -- the expected result is returned. str_split ty_tbl_str_split: = ty_tbl_str_split (); BEGIN -- the length of the split source string. len: = LENGTH (p_str); -- separates the LENGTH of a string. len1: = LENGTH (p_delimiter); -- traverses the split source string. WHILE j <len LOOP -- in the split source string, query the separator string. j: = INSTR (p_str, p_delimiter, I); IF j = 0 THEN -- j = 0 means no. -- it can be understood that the last unit is queried. -- set j: = len so that External Loop processing can end. j: = len; -- get the content of the last unit. str: = SUBSTR (p_str, I); -- append a row to the result. str_split.EXTEND; -- set the result content. str_split (str_split.COUNT): = ty_row_str_split (strValue => str); IF I> = len THEN EXIT; END IF; ELSE -- IF it is in the split source string, the separator string is found. -- first, obtain the split content. str: = SUBSTR (p_str, I, j-I); -- then set the index. During the next search, it starts from the specified index location (not from 0) i: = j + len1; -- adds a row to the result. str_split.EXTEND; -- set the result content. str_split (str_split.COUNT): = ty_row_str_split (strValue => str); end if; end loop; RETURN str_split; END fn_split;