String Functions
concat
concat( expr )
Concatenate strings. Inputs must be “string” datatypes.
Concatenate the values in column1 and column2:
table new_table is select
concat(column1, column2) as newcol1,
from original_table;
Concatenate two raw strings (“this” and “that”), using a “+” in between. Results in “this+that”:
table new_table is select
concat(literal "this", literal "+", literal "that") as newcol
from original_table;
extract
extract( expr, pattern )
The extract function will extract an array or struct from the string, depending on capture groups in the regular expression. For example, the regular expression (\w+)=(\w+) has three unnamed capture groups. When applied to the input “switchboard=awesome”, it will produce an array [“switchboard=awesome”, “switchboard”, “awesome”]. The first element of the array will always be the entire input. On the other hand, the regular expression (?
import t1 from rows {
"switchboard=awesome"
} using {
c: string
};
table t2 is select
extract(c, literal "(\w+)=(\w+)") as c1
extract(c, literal "(?<key>\w+)=(?<value>\w+)") as c2
from t1;
length
length( expr )
Returns the length of a string, as an integer.
//Create a new column called “col_length” which contains the amount of characters in column1
table new_table is select
column1,
length(column1) as col_length,
from original_table;
lower
lower( expr )
Convert the values in a string to lower case.
//Create a new column called "lower_case_column" containing lower-case versions of the values in column1
table new_table is select
column1,
lower(column1) as lower_case_column,
from original_table;
replace
replace( target, pattern, replacement )
Replace string characters.
table new_table is select
replace(column1, literal '_', literal '') as column2,
from original_table;
split
split( target, split_token )
Separates a given string value into a list using a given token such as a comma, similar to how one would treat a csv file. This function only works on instances of string
datatypes.
If the value in column1 is “a,b,c” the result of column2 below would be a list: “[‘a’,’b’,’c’]”
table new_table is select
split(column1, literal ',') as column2
from original_table;
It’s possible to just return a single value from the list. The result in column2 below would just be ‘a’ (note that lists are zero-indexed)
table new_table is select
split(column1, literal ',')[0] as column2,
from original_table;
trim
trim( expr )
Trims leading and lagging whitespace from a string.
upper
upper( expr )
Convert the values in a string to upper case.
Create a new column called “upper_case_column” containing upper-case versions of the values in column1
table new_table is select
column1,
upper(column1) as lower_case_column,
from original_table;