String Functions

CONCAT

CONCAT(string1, string2...)

Description

Concatenate one or more strings into a single string.

Example

CONCAT("Acho", "_", "Studio")

Result: Acho_Studio

ASCII

ASCII(string)

Description

Return the ASCII code value of the first character in a string.

Example

ASCII('a')

Result: 97

ASCII('acho')

Result: 97

ENDS_WITH

ENDS_WITH(string, substring)

Description

Return True if the string ends with the substring.

Example

ENDS_WITH("Acho", "o")

Result: True

ENDS_WITH("Apple", "o")

Result: False

INITCAP

INITCAP(string, [delimiters])

Description

Make the first letter of each word uppercase and the rest of the letters lowercase. Words are delimited by white space or other characters that you can specify in delimiters.

Example

INITCAP("acho studio")

Result: "Acho Studio"

INITCAP("ACHO_STUDIO", "_")

Result: "Acho_Studio"

INSTR

INSTR(string, subsstring, [position], [occurence])

Description

Return the position of the first substring in the string. 0 is returned when no match is found. Position determines where the search starts in a string. Occurrence refers to extract the i-th occurrence (ex: 2 means second occurrence).

Example

INSTR("Acho Studio", "o"),

Result: 4 (the position of the first "o")

INSTR("Acho Studio", "o", 6),

Result: 11 (the position of the first "o" from the sixth character)

INSTR("Acho Studio", "o", 1, 2)

Result: 2 (the position of the second "o" from the first character)

LEFT

LEFT(string, num_chars)

Description

Return the specified number of characters from the start of the given string.

Example

LEFT("Acho", 2)

Result: "Ac"

LENGTH

LENGTH(string)

Description

Return the length of a string in characters or bytes.

Example

LENGTH('acho')

Result: 4

LOWER

LOWER(string)

Description

Convert a text string to all lowercase letters.

Example

LOWER("ACHO")

Result: "acho"

LTRIM

LTRIM(string, [substring])

Description

Remove all leading spaces. If you want to remove strings other than spaces, you can optionally specify a substring.

Example

TRIM("   Acho   ")

Result: "Acho "

TRIM("###Acho#", "#")

Result: "Acho#"

REGEXP_CONTAINS

REGEXP_CONTAINS(string, regexp)

Description

Return True if a string is a partial match for the regular expression pattern. To see supported regular expression, visit here.

Example

REGEXP_CONTAINS("[e-mail]", r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+")

Result: True

REGEXP_CONTAINS("acho.io", r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+")

Result: False

REGEXP_EXTRACT

REGEXP_EXTRACT(string, regexp, position, occurence)

Description

Extracts the substring of the given string that matches the regular expression pattern. Returns NULL if there is no match. To see supported regular expression, visit here. Position determines where the search starts in a string. Occurrence refers to extract i-th occurrence (ex: 2 means second occurrence).

Example

REGEXP_EXTRACT("[e-mail]", r"^[a-zA-Z0-9_.+-]+")

Result: "contact"

REPLACE

REPLACE(original_string, from_value, to_value)

Description

Replace all occurrences of from_value with to_value in original_string. If the from_value is not found, the string is unchanged.

Example

REPLACE("apple pie", "pie", "cobbler")

Result: "apple cobbler"

RIGHT

RIGHT(string, num_chars)

Description

Return the specified number of characters from the end of the given string.

Example

RIGHT("Acho", 2)

Result: "ho"

RTRIM

RTRIM(string, [substring])

Description

Remove all trailing spaces. If you want to remove strings other than spaces, you can optionally specify a substring.

Example

TRIM("   Acho   ")

Result: " Acho"

TRIM("###Acho#", "#")

Result: "###Acho"

STARTS_WITH

STARTS_WITH(string, substring)

Description

Return True if the string ends with the substring.

Example

ENDS_WITH("Acho", "A")

Result: True

ENDS_WITH("Acho", "E")

Result: False

STRPOS

STRPOS(string, substring)

Description

Return the position of the substring within a specified string. If the substring occurs in several positions, it will only return the first occurrence.

Example

STRPOS('Acho Studio', 'o')

Result: 4

SUBSTR

SUBSTR(string, position, [length])

Description

Extract a substring from the given string, which starts at the position. The position refers to the position of a character in the given string. You can optionally specify the length to decide the number of characters that you want to extract.

Example

SUBSTR("Acho Studio", 3)

Return: "ho Studio"

SUBSTR("Acho Studio", 4, 5)

Return: "o Stu"

TRIM

TRIM(string, [substring])

Description

Remove all leading and trailing spaces. If you want to remove strings other than spaces, you can optionally specify a substring.

Example

TRIM("   Acho   ")

Result: "Acho"

TRIM("###Acho#", "#")

Result: "Acho"

UPPER

UPPER(string)

Description

Convert a text string to all uppercase letters.

Example

UPPER("Acho")

Result: "ACHO"