CREATE FUNCTION SPLITSTRINGFORDATAREPEATERSPLITTEXT (strstring nvarchar(5000), mxlen INT)
RETURNS TABLE (splitdata nvarchar(5000) , col1 nvarchar(500), col2 nvarchar(500), col3 nvarchar(500) ) 
LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE strstart INT;
    DECLARE strend INT; 
    DECLARE _items nvarchar(5000) ARRAY;
    DECLARE _index integer;
   	DECLARE s_Item NVARCHAR(500); 
	DECLARE s_ItemCode NVARCHAR(500); 
	DECLARE s_ItemDesc NVARCHAR(500); 
	DECLARE s_COL1 NVARCHAR(500); 
	DECLARE s_COL2 NVARCHAR(500); 
	DECLARE s_COL3 NVARCHAR(500);       
   	_index := 1;   
    strstart := 1;
    strend := LOCATE(strstring, '##'); 
    WHILE strstart < LENGTH(strstring) + 1 DO 
        IF strend = 0 THEN
            BEGIN 
                strend := LENGTH(strstring) + 2;
            END;
        END IF;        
       	s_Item := SUBSTRING(strstring, strstart, strend - strstart);
       	s_ItemCode := SUBSTRING(s_Item, 1, LOCATE(s_Item, ' * ') - 1 );       
       	s_ItemDesc := SUBSTRING(s_Item, LENGTH(s_ItemCode) + 4 , LENGTH(s_Item));
       
		SELECT "COL1" INTO s_COL1 FROM PMX_SPLITTEXTINTOTHREECOLUMNS(s_ItemDesc, mxlen);   
   		SELECT "COL2" INTO s_COL2 FROM PMX_SPLITTEXTINTOTHREECOLUMNS(s_ItemDesc, mxlen);   
    	SELECT "COL3" INTO s_COL3 FROM PMX_SPLITTEXTINTOTHREECOLUMNS(s_ItemDesc, mxlen);       

       	_items[:_index] := s_ItemCode || '|||' || s_COL1 || '|||' || s_COL2 || '|||' || s_COL3 ;                
        _index := :_index + 1;        
        strstart := strend + 2; 
        strend := LOCATE(strstring, '##', strstart);       
    end while; 
 
    rst = UNNEST(:_items) AS ("items"); 
    
    RETURN
        SELECT
			SUBSTR_REGEXPR('[^|||]+' IN "items" OCCURRENCE 1) AS "SPLITDATA",
			SUBSTR_REGEXPR('[^|||]+' IN "items" OCCURRENCE 2) AS "COL1",
			SUBSTR_REGEXPR('[^|||]+' IN "items" OCCURRENCE 3) AS "COL2",
			SUBSTR_REGEXPR('[^|||]+' IN "items" OCCURRENCE 4) AS "COL3"
       	FROM :rst;
 END;