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.

1SELECT
2    trim(both ' ' from input_text) AS trimmed,
3    lower(trim(both ' ' from input_text)) AS normalized
4FROM user_input;

Extract components from structured strings.

1SELECT
2    split_part(email, '@', 1) AS username,
3    split_part(email, '@', 2) AS domain
4FROM users;

Format data for display.

1SELECT
2    initcap(first_name) || ' ' || initcap(last_name) AS full_name,
3    lpad(employee_id::text, 6, '0') AS padded_id
4FROM employees;

Find and modify text patterns.

1SELECT
2    replace(description, 'old_term', 'new_term') AS updated_description
3FROM products
4WHERE position('old_term' in description) > 0;

• String Types
• String Pattern Matching
• Data Type Formatting Functions