Implementing split_part Function in GBase 8s: Two Practical Approaches

Source: Dev.to

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'

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 if exists
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  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.

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.

Example Usage

Take the string 'aa,bb,cc,dd' and retrieve the third field:

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

Both queries return:

cc

Summary

• GBase 8s lacks a native split_part, but the two implementations above fill the gap.
• 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.