Skip to main content
Version: Latest

String functions

This cheat sheet provides a quick reference guide for various string based functions commonly used in data analysis and programming. Each function is accompanied by its syntax, a sample usage, and a brief description.

CONCAT​

The CONCAT function concatenates one or more strings into a single string.

Syntax​

CONCAT(text, [text,...])

Sample​

CONCAT('John', ' ', 'Doe') => 'John Doe'

LEFT​

The LEFT function retrieves the first 'n' characters specified from the beginning of the input string.

Syntax​

LEFT(text, count)

Sample​

LEFT('123-456-7890', 3) => '123'

LEN​

The LEN function calculates and returns the total number of characters present in the provided string.

Syntax​

LEN(text)

Sample​

LEN('Product Description') => 19

LOWER​

The LOWER function transforms all characters in the input string to lowercase

Syntax​

LOWER(text)

Sample​

LOWER('User INPUT') => 'user input'

MID​

The MID function retrieves a substring from the input string starting at the specified position and extending for the specified count of characters.

Syntax​

MID(text, position, [count])

Sample​

MID('This is a sentence', 5, 3) => 'is '

REGEX_EXTRACT​

The REGEX_EXTRACT function searches the input string for the first occurrence of the specified regular expression pattern and returns the matched substring.

Syntax​

REGEX_EXTRACT(text, pattern)

Sample​

REGEX_EXTRACT('Error: Something went wrong', 'Error: (.*)') => 'Something went wrong'

REGEX_MATCH​

The REGEX_MATCH function evaluates whether the input string matches the specified regular expression pattern, returning 1 if there is a match and 0 if there is no match.

Syntax​

REGEX_MATCH(text, pattern)

Sample​

REGEX_MATCH('123-45-6789', '\d{3}-\d{2}-\d{4}') => 1

REGEX_REPLACE​

The REGEX_REPLACE function identifies all occurrences of the specified regular expression pattern in the input string and substitutes them with the provided replacement string.

Syntax​

REGEX_REPLACE(text, pattern, replacer)

Sample​

REGEX_REPLACE('Replace all bugs', 'bug', 'feature') => 'Replace all features'

REPEAT​

The REPEAT function duplicates the provided string the specified number of times, facilitating the creation of repeated patterns or sequences.

Syntax​

REPEAT(text, count)

Sample​

REPEAT('😃', 3) => '😃😃😃'

REPLACE​

The REPLACE function identifies all instances of a particular substring within the given string and substitutes them with another specified substring.

Syntax​

REPLACE(text, srchStr, rplcStr)

Sample​

REPLACE('Replace old text', 'old', 'new') => 'Replace new text'

The RIGHT function retrieves the last 'n' characters from the end of the input string, allowing you to extract a substring starting from the right.

Syntax​

RIGHT(text, n)

Sample​

RIGHT('file_name.txt', 3) => 'txt'

The SEARCH function identifies the position of the specified substring within the input string, returning the index if found, and 0 otherwise.

Syntax​

SEARCH(text, srchStr)

Sample​

SEARCH('user@example.com', '@') => 5

SUBSTR​

The SUBSTR function extracts a substring from the input string, starting at the specified position and optionally extending for the specified count of characters.

Syntax​

SUBSTR(text, position, [count])

Sample​

SUBSTR('Extract this text', 9, 4) => 'this'

TRIM​

The TRIM function eliminates any leading or trailing whitespaces from the input string.

Syntax​

TRIM(text)

Sample​

TRIM('   Trim this   ') => 'Trim this'

UPPER​

The UPPER function transforms all characters in the input string to uppercase.

Syntax​

UPPER(text)

Sample​

UPPER('title') => 'TITLE'

URL​

The URL function checks if the input string is a valid URL and converts it into a hyperlink

Syntax​

URL(text)

Sample​

URL('https://www.example.com') => a clickable link for https://www.example.com

URLENCODE​

The URLENCODE function percent-encodes special characters in a string so it can be substituted as a query parameter into a URL.

It is similar to JavaScript encodeURIComponent() function, except it encodes only characters that have a special meaning according to RFC 3986 section 2.2 and also percent signs and spaces; other characters such as letters from non-Latin alphabets will not be encoded. Like encodeURIComponent(), it should be used only for encoding URL components, not whole URLs.

Syntax​

URLENCODE(text)

Sample​

'https://example.com/q?param=' & URLENCODE('Hello, world')
=> 'https://example.com/q?param=Hello%2C%20world'