Text Functions

List of text functions

CONCAT
CONCAT_WS
LEN
UPPER_CASE
LOWER_CASE
LEFT
RIGHT
TRIM
TRIM_LEFT
TRIM_RIGHT
SUBSTR
RE
TEST
MATCH
REPLACE
SPLIT

Deprecated functions

STRINGFORMAT

Concatenation (CONCAT)

Adds two or more strings together.

Takes at least 1 argument.
All arguments must be strings, numbers, variables or alternatively arrays.

input = "week"

[function] --> [output]

CONCAT("pine","apple")      --> "pineapple"
CONCAT("he","ll","o")       --> "hello"
CONCAT(1,2)                 --> 12
CONCAT({input},"end")       --> "weekend"
CONCAT(1+1,"nd")            --> "2nd"
CONCAT(["a","b","c"])       --> "abc"

CONCAT()                    --> invalid

Concatenation with a separator (CONCAT_WS)

Adds two or more strings together with a separator.

Takes at least 2 arguments.
The first argument specifies the separator.
The other arguments are the values to be concatenated.
The arguments have to be strings, numbers or variables.
All arguments except the first one can also take arrays.

input = "week"

[function] --> [output]

CONCAT("-","pine","apple")      --> "pine-apple"
CONCAT_WS("+","he","ll","o")    --> "he+ll+o"
CONCAT_WS("_"1,2)               --> 1_2
CONCAT_WS(" ",{input},"end")    --> "week end"
CONCAT_WS("^",1+1,"nd")         --> "2^nd"
CONCAT_WS("separator","word")   --> "word"
CONCAT_WS("!",["a","b","c"])    --> "a!b!c"

CONCAT_WS()                     --> invalid

Length (LEN)

Returns the length of a string.

Takes 1 argument.
The argument has to be a string, number, or a variable.

[function] --> [output]

LEN("Hello")       --> 5
LEN("555")         --> 3
LEN(555)           --> 3

LEN("Hello", "bye")    --> invalid

Upper case (UPPER_CASE)

Converts a string to the upper case.

Takes 1 argument.
The argument has to be a string or a variable.

[function] --> [output]

UPPER_CASE("Hello World")       --> "HELLO WORLD"
UPPER_CASE("I AM NEW HERE")     --> "I AM NEW HERE"
UPPER_CASE("2ae3")              --> "2AE3"

UPPER_CASE("Hello", "World")       --> invalid

Lower case (LOWER_CASE)

Converts a string to the lower case.

Takes 1 argument.
The argument has to be a string or a variable.

[function] --> [output]

LOWER_CASE("Hello World")       --> "hello world"
LOWER_CASE("I AM NEW HERE")     --> "i am here"
LOWER_CASE("2AE3")              --> "2ae3"

UPPER_CASE("Hello", "World")       --> invalid

Left (LEFT)

Returns the first character of a string or the specified number of characters from the beginning.

Takes 1 or 2 arguments.
The first argument has to be a string, number or a variable.
The second optional argument specifies the number of characters to be taken from the left side. If not specified, it defaults to 1.

input = "123"

[function] --> [output]

LEFT("abcd")           --> "a"
LEFT("")               --> ""
LEFT("abcd",2)         --> "ab"
LEFT({input},2)        --> "12"

Right (RIGHT)

Returns the last character of a string or the specified number of characters from the end.

Takes 1 or 2 arguments.
The first argument has to be a string, number or a variable.
The second optional argument specifies the number of characters to be taken from the right side. If not specified, it defaults to 1.

input = "123"

[function] --> [output]

RIGHT("abcd")           --> "d"
RIGHT("")               --> ""
RIGHT("abcd",2)         --> "cd"
RIGHT({input},2)        --> "23"

Trim (TRIM)

Trims leading and trailing spaces in a string.

Takes 1 argument.
The argument has to be a string, number or a variable.

input = "  123  "

[function] --> [output]

TRIM("   abcd   ")       --> "abcd"
TRIM("")                 --> ""
TRIM("abcd")             --> "abcd"
TRIM({input})            --> "123"

Trim left (TRIM_LEFT)

Trims leading spaces in a string.

Takes 1 argument.
The argument has to be a string, number or a variable.

input = "  123"
[function] --> [output]

TRIM_LEFT("   abcd")          --> "abcd"
TRIM_LEFT("   abcd   ")       --> "abcd   "
TRIM_LEFT("")                 --> ""
TRIM_LEFT({input})            --> "123"

Trim right (TRIM_RIGHT)

Trims trailing spaces in a string.

Takes 1 argument.
The argument has to be a string, number or a variable.

VARIABLE = "123  "
[function] --> [output]

TRIM_RIGHT("abcd   ")              --> "abcd"
TRIM_RIGHT("abcd")                 --> "abcd"
TRIM_RIGHT("")                     --> ""
TRIM_RIGHT({VARIABLE})             --> "123"

Substring (SUBSTR)

Returns a substring from a specified position of a string.

Takes 2 or 3 arguments.
The first argument has to be a string or a variable.
The second argument is the index of the first character to appear in the substring
The third, optional argument is the index of the last character to appear in the substring
The second and third argument has to be a number or a variable.
If the third argument is not provided, the substring spans till the end of the original string.

[function] --> [output]

SUBSTR("ab123ABCD",3,5)          --> 123
SUBSTR("ab123ABCD",6,6)          --> "A"
SUBSTR("ab123ABCD",0,5)          --> "ab123"
SUBSTR("ab123ABCD",3)            --> "123ABCD"

SUBSTR("ab123ABCD")              --> invalid

Regular expression (RE)

Builds a regular expression for use in other text functions (TEST, MATCH, REPLACE, SPLIT).

Takes 1 or 2 arguments.
The first argument is a string representation of the regular expression.
The second optional argument is a string representation of custom flags.

[function] --> [output]

RE("[A-Z]")        --> /[A-Z]/
RE("[A-Z]","g")    --> /[A-Z]/g

Test (TEST)

Tests a string for a match against a regular expression and returns true or false.

Takes 2 arguments.
The first argument specifies to string to be tested.
The first argument has to be a string, number or a variable.
The second argument specifies the substring whose presence is to be tested.
The second argument can take a string or the RE function.

[function] --> [output]

TEST("Hello, World!","ll")             --> true
TEST("Hello, World!","[A-Z]")          --> true
TEST("Hello, World!",RE("l{2}"))       --> true
TEST("Hello, World!",RE("l{3}"))       --> false
TEST("Hello, World!",RE("H[ae]llo"))   --> true
TEST("Hello, World!",RE("h[ae]llo"))   --> false

Match (MATCH)

Tests a string for a match against a regular expression and returns an array of matches (or null for no matches).

Takes 2 arguments.
The first argument specifies to string to be tested for matches.
The first argument has to be a string, number or a variable.
The second argument specifies the regular expression to be used for the matching.
The second argument can take a string or the RE function.
When the second argument is given as a plain string, the match is performed with the g (global) flag.

[function] --> [output]

MATCH("Hello, World!","ll")             --> ["ll"]
MATCH("Hello, World!","[A-Z]")          --> ["H","W"]
MATCH("Hello, World!",RE("[A-Z]"))      --> ["H"]
MATCH("Hello, World!",RE("[A-Z]","g"))  --> ["H","W"]
MATCH("Hello, World!",RE("l{3}"))       --> null
MATCH("Hello, World!",RE("h[ae]llo"))   --> null

Replace (REPLACE)

Takes a string, matches it against a regular expression and replaces the matches with a given replacement string.

Takes 3 arguments.
The first argument specifies to string to be tested for matches.
The first argument has to be a string, number or a variable.
The second argument specifies the regular expression to be used for the matching.
The second argument can take a string or the RE function.
When the second argument is given as a plain string, the match is performed with the g (global) flag.
The third argument specifies the replacement string.

Argument 1 --> where to replace

Argument 2 --> what to replace

Argument 3 --> for what to replace

[function] --> [output]

REPLACE("Hello World", "o", "a")                    --> "Hella warld"
REPLACE("Hello, World!", "ll", "zz")                --> "Hezzo, World!"
REPLACE("Hello, World!", RE("[A-Z]","g"), "X")      --> "Xello, Xorld!"
REPLACE("John Smith", RE("(\w+)\s(\w+)"), "$2, $1") --> "Smith, John"

Split (SPLIT)

Takes a string, matches it against a regular expression and splits it into an array of strings.

Takes 2 arguments.
The first argument specifies to string to be tested for matches.
The first argument has to be a string, number or a variable.
The second argument specifies the regular expression to be used for the matching.
The second argument can take a string or the RE function.
When the second argument is given as a plain string, the match is performed with the g (global) flag.

[function] --> [output]

SPLIT("Hello, World!", "ll")                --> ["He","o, World!"]
SPLIT("Hello, World!", "l{2}")              --> ["He","o, World!"]
SPLIT("Hello, World!", RE("[A-Z]","g"))     --> ["","ello, ","orld!"]

String format (STRINGFORMAT) (deprecated)

This function is deprecated. Use plain text with variables instead.

Includes variables into a string.

Must have 1 argument, the string with variables to include.

input = "hi"

[function] --> [output]

STRINGFORMAT("Hello,World")             --> "Hello,World"
STRINGFORMAT("1, 2")                    --> 1 2
STRINGFORMAT("{INPUT}bye")              --> "hiby"
STRINGFORMAT("Hello")                   --> "Hello"
STRINGFORMAT("ha he")                   --> "ha he"

Was this helpful?