String Functions: Advanced

Raw logs often pack many fields into a single string. A URL query string reads utm_source=mail&utm_campaign=spring&ref=42, a logfmt line reads level=warn status=502, and a signature header reads t=1710513000,v1=9f86d081. All 3 share the same layout: entries separated by a delimiter, and inside each entry a key and a value separated by a second delimiter. SPLIT_TO_MAP(text, entry_delimiter, key_value_delimiter) reads that layout and returns a MAP(VARCHAR, VARCHAR), the key-value type from the data types track. In a map each key appears once and holds one value, and the subscript m['key'] returns that value, so each field becomes a lookup by name.

Order matters: the delimiter between entries comes second, and the delimiter between a key and its value comes third. Every value comes back as VARCHAR, so ref_value is the string '42', and arithmetic on it needs a CAST first, as ref_plus_one shows. Keys are case-sensitive: ['Ref'] and ['ref'] are different lookups.

A webhook log keeps each delivery's whole signature header in a single VARCHAR column. The query below keeps merchant m_7, then reads the t entry as the signing timestamp and the v1 entry as the digest. The digests here are cut to 8 characters to fit the frame; real ones run 64.

Execution order puts WHERE ahead of SELECT, so evt_103 is gone before any parsing happens. The query also calls SPLIT_TO_MAP twice per row, once per field. A CTE that parses once and names the map keeps every field a single subscript away:

SPLIT_TO_MAP is strict about layout. It returns NULL for a NULL input and accepts empty values, but any entry it cannot split into exactly 1 key and 1 value fails the entire query, every good row included. With & between entries and = inside them:

Spacing causes the quietest failures. Hand-written log lines often put a space after each ;, and the split keeps that space as part of the next key. Run each tab against the same 2 lines:

Whichever version the data calls for, the arguments keep their jobs: the text first, then the delimiter between entries, then the one between each key and its value. Swapping the last 2 on 'page=2&size=50' makes it split on = instead. Ahead of 2&size and 50 comes the entry page, which holds no &, so the query fails. Every value still comes back as VARCHAR, so a number read from the map needs a CAST before any arithmetic.

Text leaving the warehouse often has to fit exact widths: invoice numbers with a fixed count of digits, bank files where each field starts at a known column, report lines that align. LPAD(text, size, pad) adds copies of pad on the left until the result is size characters long, and RPAD adds them on the right. With CONCAT joining the pieces, the pair builds any fixed layout.

LPAD and RPAD both cut input that is already longer than size down to size characters, keeping the leftmost ones. A name loses its ending. A zero-padded ID loses its last digits, which is worse: 2 different sequence numbers can produce the same invoice number. The animation pads 5 sequence numbers to 6 digits and sorts by the result; watch the last 2 rows.

Sequences 1234567 and 1234568 both became INV-123456. The query finished cleanly, with no error or warning. Size the width for the largest value the column will ever hold, and add a check such as WHERE LENGTH(CAST(seq AS VARCHAR)) > 6 to a data quality job so an overflow is caught before invoices go out.

Text compares character by character from the left, so '9' sorts after '815': the first characters decide, and the character 9 comes after 8 whatever the lengths. Padding every value to the same width lines the digits up by place, and text order then matches numeric order. That matters wherever IDs are compared as text: file names, object storage keys, joins against a system that keeps IDs as strings.

LPAD treats a minus sign as another character, so LPAD('-42', 6, '0') returns '000-42'. Take the absolute value with ABS and pad its digits, then put the sign in front with CONCAT. For money, CAST to DECIMAL(10, 2) first so every amount shows 2 decimal places, then pad with spaces on the left so the decimal points line up down the column.

In report_line, RPAD cut 'Northwind Coffee' to 'Northwind Co' to hold its 12-character slot, and space padding leaves the refund's minus sign beside its digits. In bank_amount, the CASE writes the sign first and LPAD fills the 9 places after it with zeros.

Keep formatting in the SELECT list and keep ordering on the data. ORDER BY seq DESC sorts the integers themselves, so it stays right however large the sequence grows. Ordering by the padded text works only while every value fits the width: once 1234567 and 1234568 both read INV-123456, the text alone cannot say which invoice came later.

Some data is raw bytes with no characters behind it: an image thumbnail, an encrypted token, the output of a hash function. SQL stores bytes in VARBINARY, a type holding a sequence of byte values from 0 to 255. Raw bytes cannot travel safely through JSON or CSV, and email and HTTP headers have the same problem, because all of them expect printable text and treat some byte values as control codes. Base64 is the standard fix: it rewrites any bytes using 64 printable characters, the letters A to Z and a to z, the digits 0 to 9, plus + and /.

TO_BASE64(bytes) takes VARBINARY and returns the Base64 VARCHAR, and FROM_BASE64(text) reverses it. Text you want encoded has to become bytes first, which is the job of TO_UTF8, and decoded bytes become readable text again through FROM_UTF8. Take that pair on trust for now; the last section of this lesson explains what it does.

Each Base64 character stands for 6 bits, a value from 0 to 63 that picks a character from the alphabet. The encoder reads its input in groups of 3 bytes, which is 24 bits, and writes 4 characters for each group. Take 'Hi': its bytes are 72 and 105, 01001000 01101001 in bits, 16 bits in all, split into groups of 6:

The group came up 8 bits short, so the encoder fills the third character with zero bits and writes = in place of the fourth. A 1-byte input ends in ==, a 2-byte input in =, and a 3-byte input needs no padding:

The characters + and / already mean something inside URLs and file paths, so the URL-safe variant of Base64 writes - and _ in their places and keeps the other 62 characters. TO_BASE64URL and FROM_BASE64URL use that alphabet. The example builds the URL-safe form with 2 REPLACE calls so each swap is visible, and TO_BASE64URL(TO_UTF8('???>>>')) returns the same string in a single call. Text containing - or _ came from the URL-safe encoder, and handing it to the standard decoder raises an error.

Decoding reads inside out. In FROM_UTF8(FROM_BASE64('SGkh')), the inner FROM_BASE64 turns the 4 characters into 3 bytes, and FROM_UTF8 turns those bytes into the text 'Hi!'. Encoding runs the same 2 steps in reverse: TO_UTF8 makes the bytes, and TO_BASE64 wraps them as text. Nest a pair the other way round and the inner call receives VARCHAR where it expects VARBINARY, so the query fails to resolve.

Hex writes each byte as 2 characters from 0 to 9 and A to F, each character standing for 4 bits. It takes twice the space of the raw bytes, which is more than Base64 takes. In return every byte stays visible as its own pair, something Base64 cannot give. That's why hash digests and checksums are written in hex, the v1 value in a signature header among them, and why engineers reach for it when they need to see what bytes a column really holds.

TO_HEX(bytes) returns a VARCHAR of hex digits, always uppercase. FROM_HEX(text) accepts upper or lower case and returns the VARBINARY those digits describe. It raises an error for an odd number of digits or any character outside 0-9 and A-F.

Read hello_hex 2 digits at a time. The first digit of a pair counts 16s, the second counts 1s, and the letters A to F stand for 10 to 15, so 6C is 6 times 16 plus 12, which is 108, the byte for 'l'.

é came back as 2 pairs, C3A9, because UTF-8 spends 2 bytes on it; the next section explains why.

A hash function such as SHA256(bytes) returns its digest as 32 bytes of VARBINARY. Raw bytes cannot sit safely in a header or a JSON field, as the Base64 section showed, and a text column has the same limit, so senders write those 32 bytes out as 64 hex characters, and that text is what you end up comparing against. Many senders write it in lowercase, while TO_HEX writes uppercase, and a plain string comparison fails on every row even when the bytes agree. In the switcher, computed_hex is your side's TO_HEX output for each event's SHA-256 digest, sent_digest is the partner's lowercase copy, and each tab checks them a different way.

Some hex text encodes a number: a color such as #1E90FF, a hex ID, a memory address in an error log. FROM_HEX returns bytes, which is the wrong shape there. FROM_BASE(text, 16) reads the digits as a BIGINT, and TO_BASE(number, 16) writes a number back as lowercase hex. A color gives each channel 2 hex digits, red first, then green, then blue, so SUBSTR, the short spelling of SUBSTRING, slices each channel out first.

When hex stands for bytes that encode text, getting the text back takes 2 nested calls: FROM_HEX turns the digits into VARBINARY, and FROM_UTF8 reads those bytes as characters. FROM_UTF8(FROM_HEX('436166C3A9')) returns 'Café', 5 bytes and 4 characters, with C3A9 as the é. FROM_BASE on the same 10 digits would return one large BIGINT, a number with no characters in it.

A VARCHAR holds characters. Anything written to disk or sent over a network is bytes. UTF-8 is the rule that maps each Unicode character to between 1 and 4 bytes, and every byte conversion in this lesson has passed through it: TO_UTF8(text) returns the UTF-8 bytes of a string as VARBINARY, and FROM_UTF8(bytes) reads them back. Usually the 2 sides agree. The places where they part ways are where pipelines break.

The first byte announces the length. A byte from 00 to 7F is a whole character, which is why plain ASCII text has identical bytes in UTF-8. C2 to DF starts a 2-byte character, E0 to EF a 3-byte one, F0 to F4 a 4-byte one, and 80 to BF can only continue a character already started. A decoder uses those ranges to find boundaries, and to notice bytes that break the rules.

LENGTH on a VARCHAR counts characters. LENGTH on VARBINARY counts bytes, so LENGTH(TO_UTF8(name)) is a string's size on the wire. For English names the 2 numbers match, which hides the difference until the data reaches a market with accents or another script.

VARCHAR(n) limits characters, so all 4 names fit a VARCHAR(16) column here. A partner file format that caps the field at 16 bytes rejects 2 of them. Check exports against the receiver's unit: WHERE LENGTH(TO_UTF8(display_name)) > 16 lists the rows that will bounce.

Trimming to a byte cap with SUBSTR on VARBINARY counts bytes, and the cut can land inside a character. The query below shows 'Café' as bytes and cuts it at 4: the bytes 43 61 66 C3 A9 lose A9, which leaves C3, a lead byte whose partner is gone. Cutting the VARCHAR at 4 counts characters instead and keeps the é whole.

Decoding those 4 bytes is where the damage shows. FROM_UTF8 replaces any invalid sequence with U+FFFD, the replacement character drawn as �, so FROM_UTF8(SUBSTR(TO_UTF8('Café'), 1, 4)) returns 'Caf�'. A second argument sets a different replacement, and an empty string drops the bad bytes: FROM_UTF8(SUBSTR(TO_UTF8('Café'), 1, 4), '') returns 'Caf'. To trim safely, cut the VARCHAR with SUBSTR and check its byte length afterwards.

Older systems often write Latin-1, where é is the single byte E9. To a UTF-8 reader, E9 announces a 3-byte character, and nothing after it continues one, so the decode produces �. An import table holds each name's raw bytes as hex: row 1 is 436166C3A9, row 2 is 436166E9 from a Latin-1 source, and row 3 is 4B6169. Comparing a default decode with one that marks bad bytes as ? flags the broken rows, because the 2 versions differ only where invalid bytes were replaced.

Against a 16-byte cap, the 2 filters disagree on the merchants table. LENGTH(display_name) > 16 returns no rows, since the longest name runs 15 characters. LENGTH(TO_UTF8(display_name)) > 16 returns Pizzeria 🍕 Roma and 東京ラーメン at 18 bytes each, the 2 rows the partner would reject.