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 (?\w+)=(?\w+) contains two named capture groups, and when applied to the same input it will yield a structure {key: "switchboard", value: "awesome"}. You may extract individual array elements and structure fields in subsequent select statements.

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;