String Functions: Advanced

Parse key-value pairs from raw text

SPLIT_TO_MAP parses a string containing key-value pairs into a map data structure. The function takes three arguments: the string to parse, the delimiter between pairs, and the delimiter between keys and values. Once parsed, you can access individual values by their keys using bracket notation.

The syntax is SPLIT_TO_MAP(string, entry_delimiter, key_value_delimiter). The entry_delimiter separates different key-value pairs (commonly "&" or ","). The key_value_delimiter separates each key from its value (commonly "=" or ":"). The result is a MAP type that supports key-based lookups.

SPLIT_TO_MAP works with any consistently delimited key-value string. The most common patterns are URL query strings and configuration entries.

SPLIT_TO_MAP parses the query string into a map. The bracket notation ['user_id'] extracts the value for that specific key. This converts unstructured text into structured, queryable data without complex string manipulation.

Configuration data often arrives as delimited strings from external systems. SPLIT_TO_MAP handles various delimiter formats:

Here the entry delimiter is a comma and the key-value delimiter is a colon. The function adapts to whatever format your source data uses. You define the parsing rules, and SPLIT_TO_MAP applies them consistently.

Beyond simple extraction, SPLIT_TO_MAP results can be used directly in queries for filtering, joining, and aggregation.

Once parsed, map values can be used in WHERE clauses for filtering:

Choosing the right tool matters. SPLIT_TO_MAP shines with structured key-value data but is not a replacement for a proper JSON parser.

SPLIT_TO_MAP is particularly useful when working with serialized key-value data stored as strings.

URL Query Strings

Parse parameters from web logs and analytics data

Configuration Values

Extract settings from serialized config strings

External API Data

Process key-value pairs from API responses

Embedded Filters

Filter records based on embedded parameter values

SELECT
  call_id,
  ___(endpoint,  ___,  ___) 'action' AS action
FROM api_calls

SPLIT_TO_MAP converts flat key-value strings into queryable maps, turning text parsing into structured data access.

Accessing a non-existent key in the resulting map returns NULL, not an error. Use COALESCE to provide defaults for missing keys.

Format strings with padding and alignment

CONCAT combines multiple strings into a single result. Combined with LPAD for zero-padding and CAST for number formatting, you can build formatted strings with precise control over their appearance. This provides the flexibility to create display strings, report labels, and standardized identifiers directly within SQL queries.

The syntax is CONCAT(value1, value2, value3, ...). The LPAD(string, length, pad_char) function pads a string to a fixed width by prepending pad characters. ROUND(number, decimals) rounds to a specific number of decimal places.

CONCAT is the foundation for building formatted output. It assembles literal text and column values in any order.

CONCAT combines a dollar sign literal with the rounded price. The ROUND function ensures prices display with consistent precision.

ROUND ensures monetary values display with exactly two decimal places. Combine it with CONCAT and a dollar sign prefix:

The ROUND function with 2 decimal places ensures consistent formatting. This pattern works reliably across different numeric precisions in your source data.

LPAD pads a value with leading zeros to create fixed-width identifiers that sort correctly and display consistently:

LPAD pads the order ID to 6 characters using zeros. Combined with CONCAT, this creates uniform identifiers like ORD-000042.

CONCAT assembles multiple values in order, creating descriptive labels:

Multiple CONCAT arguments combine text literals with column values. CAST converts integers to strings so CONCAT can join them.

Building formatted strings in SQL is powerful, but readability matters. Use clear variable names and keep string operations simple.

CONCAT with LPAD and ROUND excels at producing human-readable output with consistent styling.

Always CAST numeric values to VARCHAR before concatenating. Mixing types in CONCAT requires explicit conversion.

CONCAT with NULL values produces NULL output. If any input is NULL, the result is NULL. Use COALESCE to provide default values for potentially NULL columns.

SELECT
  product_name,
  ___('$', ROUND(___, 2)) AS display_price
FROM products

CONCAT combines values in the order provided. For numeric formatting, use ROUND to control decimal places.

Always CAST non-string values to VARCHAR when concatenating. This ensures consistent behavior across different data types.

LPAD pads strings to fixed widths using a specified character, ideal for creating sortable identifiers.

Encode and decode binary data as text

Base64 encoding converts binary data into a text representation using 64 printable ASCII characters. TO_BASE64 encodes binary data to Base64 text. FROM_BASE64 decodes Base64 text back to binary. These functions enable safe storage and transmission of binary data through text-only channels.

The syntax is TO_BASE64(varbinary) for encoding and FROM_BASE64(varchar) for decoding. Base64 uses characters A-Z, a-z, 0-9, plus (+), and slash (/). The encoded output is approximately 33% larger than the original binary because every 3 bytes become 4 characters.

To encode a text string, first convert it to binary using TO_UTF8, then encode to Base64:

TO_UTF8 converts the string to bytes, then TO_BASE64 converts those bytes to Base64 text. This two-step process is required because TO_BASE64 expects binary input, not string input.

Decoding reverses the process: FROM_BASE64 produces binary, then FROM_UTF8 converts to text:

Example: FROM_UTF8(FROM_BASE64('SGVsbG8gV29ybGQ=')) returns 'Hello World'. The decoding chain must reverse the encoding chain exactly.

TO_BASE64 and FROM_BASE64 serve opposite purposes in data pipelines, often used at system boundaries.

SELECT
  call_id,
  ___(___(err_msg)) AS decoded
FROM api_calls

The decoding chain must reverse the encoding chain exactly: FROM_BASE64 produces bytes, FROM_UTF8 interprets those bytes as text.

Base64-encoded data is approximately 33% larger than the original because every 3 bytes become 4 Base64 characters.

Convert between hex and readable formats

Hexadecimal (hex) encoding represents binary data using characters 0-9 and A-F. TO_HEX converts binary data to a hexadecimal string. FROM_HEX converts a hexadecimal string back to binary. Each byte becomes exactly two hex characters, making hex useful for debugging and byte-level inspection.

The syntax is TO_HEX(varbinary) for encoding and FROM_HEX(varchar) for decoding. Hex encoding doubles the size of the data (each byte becomes two characters) but provides a direct byte-by-byte representation that is easy to read and manipulate.

To see the hex representation of text, first convert to bytes with TO_UTF8:

Decoding reverses the hex encoding. FROM_UTF8(FROM_HEX('44617461')) returns 'Data'. The hex string is converted to bytes, then those bytes are interpreted as UTF-8 text.

Debug Encoding

Inspect byte sequences to diagnose encoding issues

Color Codes

Work with RGB color codes and hash values

Byte Validation

Validate data at the byte level for integrity

TO_HEX processes each byte, outputting two characters. FROM_HEX expects pairs of valid hex characters (0-9, A-F). Hex input is case-insensitive, and the output is exactly double the input size.

SELECT
  kv_value,
  ___(___(kv_value)) AS hex_bytes
FROM kv_store

The encoding chain for hex is always text -> TO_UTF8 -> TO_HEX. Each byte becomes exactly two hex characters.

TO_HEX encoding is useful for debugging encoding issues because it reveals the exact bytes stored in a string.

Handle character encoding conversions

UTF-8 is the dominant character encoding on the internet. TO_UTF8 converts a string to its UTF-8 byte sequence (varbinary). FROM_UTF8 converts a UTF-8 byte sequence back to a string. These functions bridge between text and binary representations, enabling precise encoding control.

The syntax is TO_UTF8(varchar) to get bytes and FROM_UTF8(varbinary) to get text. UTF-8 uses 1 to 4 bytes per character: ASCII characters use 1 byte, common international characters use 2-3 bytes, and emojis use 4 bytes. This variable-length encoding is efficient and universally supported.

TO_UTF8 is required before any byte-level operation like Base64 or hex encoding:

Example: LENGTH(TO_UTF8(text_col)) returns the byte count for a text column, which may differ from character count for non-ASCII text. FROM_UTF8(binary_col) interprets a byte sequence as UTF-8 text.

The pattern is always: text -> TO_UTF8 -> bytes -> encoding function. Decoding reverses: encoded -> decoding function -> bytes -> FROM_UTF8 -> text. This chain preserves the original data exactly.

FROM_UTF8 fails on invalid byte sequences. Some databases offer an optional replacement parameter:

FROM_UTF8(binary_data, '') with a second parameter specifies what to substitute for invalid bytes. An empty string removes them. A question mark or similar character makes corruption visible. This is useful when processing data from systems with inconsistent encodings.

SELECT
  kv_value,
  ___(___(kv_value)) AS byte_count
FROM kv_store

Character count and byte count differ for non-ASCII text. The word Café has 4 characters but 5 bytes because the accented é uses 2 bytes.

LENGTH(TO_UTF8(col)) is the standard way to measure actual storage size, essential for capacity planning with international text.

When importing data from external sources, round-tripping through TO_UTF8 and FROM_UTF8 reveals encoding corruption.