Extracting Characters from the Left Side

Use left(source_text, char_count) to obtain a specified number of charcaters from the start of a string.

SELECT LEFT('datastream_process', 9);
-- Result: datastrea

Extracting Characters from the Right Side

Use right(source_text, char_count) to retrieve a defined number of characters from the end of a string.

SELECT RIGHT('datastream_process', 7);
-- Result: process

Extracting a Segment by Position

mid(orig_str, start_pos) returns all characters from a given position to the end; mid(orig_str, start_pos, seg_len) limits the result to a fixed length. Indexing starts at 1 for forward direction and -1 for reverse.

-- From the third character onward
SELECT MID('datastream_process', 3);
-- Result: astream_process

-- Last three characters
SELECT MID('datastream_process', -3);
-- Result: ess

-- Five characters beginning at position 3
SELECT MID('datastream_process', 3, 5);
-- Result: astre

-- Attempt five characters starting three positions from the end (truncated if insufficient length)
SELECT MID('datastream_process', -3, 5);
-- Result: ess

Splitting Strings by a Delimiter

split_by_token(txt, separator, occurrence) fetches portions of text based on delimiter frequency. Positive occurrence counts from the start, ngeative from the end.

-- Content before the second pipe symbol
SELECT SPLIT_BY_TOKEN('alpha|beta|gamma|delta', '|', 2);
-- Result: alpha|beta

-- Content after the second-last pipe symbol
SELECT SPLIT_BY_TOKEN('alpha|beta|gamma|delta', '|', -2);
-- Result: gamma|delta

-- Delimiter not found; entire input returned unchanged
SELECT SPLIT_BY_TOKEN('alpha|beta|gamma|delta', '-', 2);
-- Result: alpha|beta|gamma|delta

Transforming Delimited Values into Labeled Assignments

To map segments of a delimited string to labeled variables:

SELECT CONCAT(
    'x=', SPLIT_BY_TOKEN('10|20|30', '|', 1),
    ' y=', SPLIT_BY_TOKEN(SPLIT_BY_TOKEN('10|20|30', '|', 2), '|', -1),
    ' z=', SPLIT_BY_TOKEN('10|20|30', '|', -1)
);
-- Result: x=10 y=20 z=30