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 using capture groups specified in the given regular expression.

import t1 from rows {
    "switchboard=awesome"
} using {
    c: string
};

table t2 is select
       //unnamed capture groups
        extract(c, literal "(\w+)=(\w+)") as c1
from t1;

See more details and examples

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 matching the given regular expression pattern with the supplied replacement value.

In the example below, the underscore character (_) is removed by replacing it with an empty character.

table new_table is select
    replace(column1, literal '_', literal '') as column2,
from original_table;

See more details and examples

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;