String Functions

Functions for manipulating text strings.

SPLIT

Splits the first string argument into an array of strings, using the second string argument as the delimiter.

Inputs:

STRINGSTRING

Output:

LIST<STRING>

Purpose:

property

Example:

SELECT SPLIT(product_tags, ',') AS tag_list;

UPPER

Converts the input string to all uppercase letters according to locale rules.

Inputs:

STRING

Output:

STRING

Purpose:

property

Example:

SELECT UPPER(country_code) AS normalized_country;

LOWER

Converts the input string to all lowercase letters according to locale rules.

Inputs:

STRING

Output:

STRING

Purpose:

property

Example:

SELECT LOWER(email_address) AS normalized_email;

CONTAINS

Returns true if the first string contains the second string as a substring, false otherwise. Case-sensitive.

Inputs:

STRINGSTRING

Output:

BOOL

Purpose:

property

Example:

SELECT description WHERE CONTAINS(description, 'urgent');

STRPOS

Returns the starting position (1-based index) of the first occurrence of the second string within the first string. Returns 0 if the substring is not found. Case-sensitive.

Inputs:

STRINGSTRING

Output:

INTEGER

Purpose:

property

Example:

SELECT STRPOS(url, '?') AS query_start_pos;

Extracts a substring from the first argument, starting at the 1-based index specified by the second argument, for a length specified by the third argument.

Inputs:

STRINGINTEGERINTEGER

Output:

STRING

Purpose:

property

Example:

SELECT SUBSTRING(phone_number, 2, 3) AS area_code; # Assuming format like +1XXX...

Concatenates two or more string arguments together. NULL arguments are typically ignored or result in NULL depending on implementation.

Inputs:

STRING...

Output:

STRING

Purpose:

property

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name;

Returns the length of a string (number of characters), the number of elements in an array/list, or the number of key-value pairs in a map.

Inputs:

STRINGARRAYMAPLIST

Output:

INTEGER

Purpose:

property

Example:

SELECT LENGTH(comment) AS comment_length;

Performs case-sensitive SQL LIKE pattern matching. Use '%' for any sequence of zero or more characters and '_' for any single character.

Inputs:

STRINGSTRING

Output:

BOOL

Purpose:

property

Example:

SELECT product_name WHERE LIKE(product_name, 'Model %');

Performs case-insensitive SQL LIKE pattern matching (similar to Postgres ILIKE). Use '%' and '_' wildcards as with LIKE.

Inputs:

STRINGSTRING

Output:

BOOL

Purpose:

property

Example:

SELECT email WHERE ILIKE(email, '%@example.com');

Replaces all occurrences of a specified substring with another substring within a string.

Inputs:

STRINGSTRINGSTRING

Output:

STRING

Purpose:

property

Example:

SELECT REPLACE(description, 'old', 'new') AS updated_description;

Removes leading and trailing whitespace or a specified set of characters from a string.

Inputs:

STRINGSTRING

Output:

STRING

Purpose:

property

Example:

SELECT TRIM(product_name) AS trimmed_name;

Returns true if a string matches a regular expression.

Inputs:

STRINGSTRING

Output:

BOOL

Purpose:

property

Example:

SELECT REGEXP_CONTAINS(email, '@.+[.]com') AS is_com_email;

Extracts a substring that matches a regular expression pattern.

Inputs:

STRINGSTRINGINTEGER

Output:

STRING

Purpose:

property

Example:

SELECT REGEXP_EXTRACT(url, '//(www[.])?([^/]+)', 2) AS domain;

Replaces substrings that match a regular expression with another string.

Inputs:

STRINGSTRINGSTRING

Output:

STRING

Purpose:

property

Example:

SELECT REGEXP_REPLACE(phone, '(d{3})-(d{3})-(d{4})', '(\1) \2-\3') AS formatted_phone;