CREATE FUNCTION PMX_SPLITTEXTINTOTHREECOLUMNS (
    INPUTTEXT NVARCHAR(5000), MXLEN INT
)
RETURNS TABLE (COL1 NVARCHAR(500), COL2 NVARCHAR(500), COL3 NVARCHAR(500))
LANGUAGE SQLSCRIPT
AS
BEGIN
    -- Initialize variables
    DECLARE REMAININGTEXT NVARCHAR(5000) := TRIM(INPUTTEXT);
    DECLARE CURRENTPART NVARCHAR(500);
    DECLARE SPACEINDEX INT;
   	DECLARE I INT;
   
	DECLARE COL1 NVARCHAR(500);
	DECLARE COL2 NVARCHAR(500);
	DECLARE COL3 NVARCHAR(500);
    -- Initialize output columns
    COL1 = NULL;
    COL2 = NULL;
    COL3 = NULL;

    -- Helper logic to find the last space within a string up to a maximum length
    SPACEINDEX = 0;
    FOR I IN 1 .. MXLEN  DO
        IF SUBSTRING(REMAININGTEXT, I, 1) = ' ' THEN
            SPACEINDEX = I;
        END IF;
    END FOR;

    -- Process first column
    IF LENGTH(REMAININGTEXT) > 0 THEN
        IF LENGTH(REMAININGTEXT) <= MXLEN THEN
            COL1 = REMAININGTEXT;
            REMAININGTEXT = '';
        ELSE
            CURRENTPART = LEFT(REMAININGTEXT, MXLEN);
            IF SPACEINDEX > 0 THEN
                COL1 = LEFT(CURRENTPART, SPACEINDEX - 1);
            ELSE
                COL1 = CURRENTPART;
            END IF;
            REMAININGTEXT = TRIM(SUBSTRING(REMAININGTEXT, LENGTH(COL1) + 1));
        END IF;
    END IF;

    -- Update SPACEINDEX for the remaining text
    SPACEINDEX = 0;
    FOR I IN 1 .. MXLEN DO
        IF SUBSTRING(REMAININGTEXT, I, 1) = ' ' THEN
            SPACEINDEX = I;
        END IF;
    END FOR;

    -- Process second column
    IF LENGTH(REMAININGTEXT) > 0 THEN
        IF LENGTH(REMAININGTEXT) <= MXLEN THEN
            COL2 = REMAININGTEXT;
            REMAININGTEXT = '';
        ELSE
            CURRENTPART = LEFT(REMAININGTEXT, MXLEN);
            IF SPACEINDEX > 0 THEN
                COL2 = LEFT(CURRENTPART, SPACEINDEX - 1);
            ELSE
                COL2 = CURRENTPART;
            END IF;
            REMAININGTEXT = TRIM(SUBSTRING(REMAININGTEXT, LENGTH(COL2) + 1));
        END IF;
    END IF;

    -- Update SPACEINDEX for the remaining text
    SPACEINDEX = 0;
    FOR I IN 1 .. MXLEN DO
        IF SUBSTRING(REMAININGTEXT, I, 1) = ' ' THEN
            SPACEINDEX = I;
        END IF;
    END FOR;

    -- Process third column
    IF LENGTH(REMAININGTEXT) > 0 THEN
        IF LENGTH(REMAININGTEXT) <= MXLEN THEN
            COL3 = REMAININGTEXT;
            REMAININGTEXT = '';
        ELSE
            CURRENTPART = LEFT(REMAININGTEXT, MXLEN);
            IF SPACEINDEX > 0 THEN
                COL3 = LEFT(CURRENTPART, SPACEINDEX - 1);
            ELSE
                COL3 = CURRENTPART;
            END IF;
            REMAININGTEXT = TRIM(SUBSTRING(REMAININGTEXT, LENGTH(COL3) + 1));
        END IF;
    END IF;

    RETURN SELECT :COL1 AS COL1, :COL2 AS COL2, :COL3 AS COL3 FROM DUMMY;
END;
