Skip to content
Blog

Text functions

Text functions are used to manipulate text. The following operators are valid for text fields.

OperatorDescriptionExampleResult
[index]alias of array extractSTRING("TEXT")[1]"T"
[begin:end]alias of array sliceSTRING("TEXT")[1:3]"TEX"

The below functions are available for text fields.

FunctionDescriptionExampleResult
array_extract(list, index)extracts a single character from string using 1-based indexarray_extract("year", 2)"e"
array_slice(list, begin, end)slices a string using 1-based indexarray_slice("year", 1, 4)"year"
concat(string1, string2, string3, ...)concatenates multiple stringsconcat("university", " of", " waterloo")"university of waterloo"
ends_with(string1, string2)alias of suffixends_with("best student", "student")true
lower(string)returns the string in lower caselower(“WaterLoo”)“waterloo”
lcase(string)alias of lowerlcase("UPPER CASE")"upper case"
left(string, count)returns the left most count number of characters from stringleft("long string", 2)"lo"
levenshtein(s1, s2)returns the minimum number of single-character edits
(insertions, deletions or substitutions) required to transform
string s1 to s2 (case-insensitive).
levenshtein('kitten', 'sitting')3
size(string)returns the number of characters in stringsize("database")8
list_element(string, index)alias of array_extractlist_element("university", 3)"i"
list_extract(string, index)alias of array_extractlist_extract("waterloo", 2)"a"
lpad(string, count, character)pads the string with the character from the left until it has count characterslpad("WaterLoo", 10, ">")">>WaterLoo"
ltrim(string)removes any white spaces on the left of the stringltrim(" waterloo ")"waterloo "
prefix(string, search_string)returns whether the string starts with search_stringprefix("good university", "good")True
repeat(string, count)repeats the string count number of timesrepeat("##", 5)"##########"
reverse(string)reverses the stringreverse("<<12345>>")">>54321<<"
right(string, count)returns the right most count number of characters from stringright("toronto", 2)"to"
rpad(string, count, character)pads the string with the character from the right until it has count charactersrpad("toronto", 10, '<')"toronto<<<"
rtrim(string)removes any white spaces on the right of the stringrtrim(" toronto ")" toronto"
starts_with(string1, string2)alias of prefixstarts_with("best student", "best")True
substring(string, start, length)extracts the string from start position until length number of characters using 1-based indexsubstring("toronto", 1, 2)"to"
substr(string, start, length)alias of substringsubstr("long str", 2, 3)"ong"
suffix(string, search_string)returns whether the string ends with search_stringsuffix("toronto12", "12")True
trim(string)removes any white spaces either on the left or right of the stringtrim(" good ")"good"
upper(string)returns the string in upper caseupper("small case")"SMALL CASE"
ucase(string)alias of upperucase("small case")"SMALL CASE"
initcap(string)returns the string with only the first letter in uppercaseinitcap("roma")"Roma"