Implementing split_part Function in GBase 8s: Two Practical Approaches

GBase 8s does not provide a built‑in split_part function like PostgreSQL. However, using the existing SUBSTRING_INDEX function or manual character parsing, we can easily achieve the same functionality. This article introduces two implementation methods with practical examples.

1. What Does split_part Do? The split_part function splits a string by a delimiter and returns the n‑th field.

Example:
split_part('aa,bb,cc,dd', ',', 3) → 'cc'

1. Method 1: Using SUBSTRING_INDEX GBase 8s provides SUBSTRING_INDEX, which extracts a substring up to a specified number of occurrences. This makes the implementation very straightforward.

DROP FUNCTION IF EXISTS split_part2;

CREATE FUNCTION split_part2(
    str_in lvarchar(2048),
    separator_in CHAR(1),
    field_in INT
) RETURNING VARCHAR(255);

    DEFINE str_len INT;
    DEFINE pos_curr INT;
    DEFINE count_field INT;
    DEFINE pos_char CHAR(1);

    IF field_in <= 0 THEN RETURN NULL; END IF;

    LET count_field = 1;
    LET str_len = LENGTH(str_in);

    -- Count delimiters to know total fields
    FOR pos_curr = 1 TO str_len
        LET pos_char = SUBSTR(str_in, pos_curr, 1);
        IF pos_char = separator_in THEN
            LET count_field = count_field + 1;
        END IF;
    END FOR;

    -- If requested field exceeds actual count, return NULL
    IF field_in > count_field THEN
        RETURN NULL;
    ELSE
        -- Take the first `field_in` segments, then the last one
        RETURN SUBSTRING_INDEX(
            SUBSTRING_INDEX(str_in, separator_in, field_in),
            separator_in,
            -1
        );
    END IF;

END FUNCTION;

Pros: Simple logic, uses built‑in functions, easy to read.
Cons: Requires one pass to count delimiters (negligible for most strings).

1. Method 2: Pure Character Parsing This version does not rely on SUBSTRING_INDEX — it manually scans the string to locate the desired field.

DROP FUNCTION IF EXISTS split_part;

CREATE FUNCTION split_part(
    str_in lvarchar(2048),
    separator_in CHAR(1),
    field_in INT
) RETURNING VARCHAR(255);

    DEFINE res VARCHAR(255);
    DEFINE str_len INT;
    DEFINE pos_curr INT;
    DEFINE substr_start INT;
    DEFINE substr_length INT;
    DEFINE pos_char CHAR(1);

    IF field_in <= 0 THEN RETURN ''; END IF;

    LET res = '';
    LET substr_start = 0;
    LET substr_length = 0;
    LET str_len = LENGTH(str_in);

    FOR pos_curr = 1 TO str_len
        LET pos_char = SUBSTR(str_in, pos_curr, 1);
        IF pos_char = separator_in THEN
            LET field_in = field_in - 1;
        END IF;

        -- Locate start of the target field
        IF field_in = 1 AND substr_start = 0 THEN
            LET substr_start = pos_curr + DECODE(pos_char, separator_in, 1, 0);
        END IF;

        -- Locate end of the target field
        IF field_in <= 0 THEN
            LET substr_length = pos_curr;
            EXIT FOR;
        END IF;
    END FOR;

    -- Handle edge cases (field is the last one or string ends)
    IF substr_length = 0 THEN
        LET substr_length = str_len + 1;
    END IF;
    IF substr_start = 0 THEN
        LET substr_start = str_len + 1;
    END IF;
    IF substr_length < substr_start THEN
        LET substr_length = 0;
    ELSE
        LET substr_length = substr_length - substr_start;
    END IF;

    RETURN NVL(SUBSTRING(str_in FROM substr_start FOR substr_length), '');

END FUNCTION;

Pros: No dependency on SUBSTRING_INDEX; fully portable logic.
Cons: More complex code; requires careful handling of positions.

1. Example Usage Take the string:'aa,bb,cc,dd' Call both functions to get the third field:

SELECT split_part2('aa,bb,cc,dd', ',', 3) FROM dual;
SELECT split_part('aa,bb,cc,dd', ',', 3) FROM dual;

Both return:
cc

1. Summary Although GBase 8s lacks a native split_part, we’ve built two reliable implementations.

Method 1 is shorter and cleaner — ideal for most cases.

Method 2 offers independence from any specific built‑in function.

Both functions have been tested and are ready to use in your GBase 8s environment. Have other string processing needs? Feel free to drop a comment below! 👇