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'

RIGHT

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'

SEARCH

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'

ISBLANK

The ISBLANK function checks if a given input is not empty or null, returning FALSE if the input has a value, and TRUE otherwise.

Syntax

ISBLANK(text)

Sample

ISBLANK('') => true
ISBLANK('Hello') => false

ISNOTBLANK

The ISNOTBLANK function checks if a given input is not empty or null, returning TRUE if the input has a value, and FALSE otherwise.

Syntax

ISNOTBLANK(text)

Sample

ISNOTBLANK('') => false
ISNOTBLANK('Hello') => true