String Functions and Operators

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Functions and operators for examining and manipulating string values. Use string functions to:

Transform case by converting strings to uppercase, lowercase, or title case.
Extract and search substrings, find positions, and split text.
Pad and trim strings to adjust length and remove unwanted characters.
Encode and hash data for storage or security purposes.

Character and byte length: Functions like char_length count characters, but octet_length counts bytes. This distinction matters for multi-byte UTF-8 characters.
1-based indexing: String positions start at 1, not 0.
NULL handling: The || operator returns NULL if either operand is NULL. Use concat to ignore NULL values.

For more information about supported types, see String Types.

Combine strings and values:

String Concatenation Operator (||) — Concatenate two strings or a string a non-string value (NULL-sensitive).
CONCAT — Concatenate multiple values, ignoring NULLs.

Measure string size:

BIT_LENGTH — Get the number of bits in a string.
CHAR_LENGTH / CHARACTER_LENGTH — Count the number of characters in a string.
LENGTH — Count the number of characters in a string.
OCTET_LENGTH — Get the number of bytes in a string.

Transform string case:

LOWER — Convert to lowercase.
UPPER — Convert to uppercase.
INITCAP — Capitalize the first letter of each word.

Extract and locate substrings:

SUBSTRING — Extract a substring by position and length.
SUBSTR — Extract a substring (alternative syntax).
LEFT — Get the first n characters.
RIGHT — Get the last n characters.
POSITION — Find the location of a substring.
STRPOS — Find substring location (alternative syntax).
OVERLAY — Replace a substring at a position.
SPLIT — Split a string into an array by delimiter.
SPLIT_PART — Split a string on delimiter and return a field.

Adjust string length:

TRIM — Remove characters from start, end, or both.
BTRIM — Remove characters from both ends.
LTRIM — Remove characters from the start.
RTRIM — Remove characters from the end.
LPAD — Pad a string on the left.
RPAD — Pad a string on the right.
SPACE — Generate a string of spaces.

Transform and manipulate strings:

REPLACE — Replace all occurrences of a substring.
REPEAT — Repeat a string multiple times.
REVERSE — Reverse the characters in a string.

Check for substrings and string boundaries:

CONTAINS / ICONTAINS — Check whether a string contains a substring.
STARTS_WITH — Check whether a string starts with a prefix.
ENDS_WITH — Check whether a string ends with a suffix.

Convert between characters and codes:

ASCII — Get the ASCII code of the first character.
CHR — Get the character for an ASCII code.

Encode data and compute hashes:

ENCODE — Encode binary data to text (base64, hex, escape).
DECODE — Decode text to binary data.
TO_BASE64 — Convert data to base64.
TO_HEX — Convert data to hexadecimal.
MD5 — Compute the MD5 hash for data.
SHA256 — Compute the SHA-256 hash for data.

Prepare strings for SQL statements:

QUOTE_IDENT — Quote a string as an SQL identifier.
QUOTE_LITERAL — Quote a string as an SQL literal.

Normalize and clean user input.

Extract components from structured strings.

Format data for display.

Find and modify text patterns.