String & Number Operators

ROUND

Specify the number of decimal points to display

ROUND(<field>, <decimal points>)

select round(sent,1)

NORMALIZE_RANGE

Wraps a numeric value so that it falls within the range from min up to (but not including) max. Values below min wrap around from the top of the range, and values at or above max wrap around from the bottom. A common use is shifting and wrapping hour-of-day values.

NORMALIZE_RANGE(<field name>, <min>, <max>)

 select normalize_range(hour, 0, 24)

SUBSTRING

Substring between start and end indexes.

SUBSTRING(<field to check against>, < startIndex>,< length>)

 select substring(Message Type,0,10)

SUBSTRING_BEFORE

Substring before the first occurrence of a delimiter for a field value.

SUBSTRING_BEFORE(<field to check against>, < delimiter>)

 select substring_before(Message Type,someDelim)

SUBSTRING_AFTER

Substring after the first occurrence of a delimiter for a field value.

SUBSTRING_AFTER(<field to check against>, < delimiter>)

  select substring_after(Message Type,someDelim)

CONCAT

Concatenates multiple columns together. When a field name does not exist in the current dataset, a fixed string is used.

CONCAT(<field name>, < anotherfield>, < yetanotherfield>,...)

 select concat(Customer, for Week of, Week)

CONV

For converting hex strings into integers.

CONV (<field name>, <field radix>, <desired radix>)

 select conv(hexfield, 16, 10)

SPLIT

Split a string of elements separated by separator into an array. If separator is not specified, comma will be used.

SPLIT(<field name>, <separator>)

 select split(Customer, ",")

ARRAY_TO_STRING

Join elements of an array value together separated by separator. When a field name does not exist in the current dataset, a fixed string is used.

ARRAY_TO_STRING(<field name>, <separator>)

 select array_to_string(Customer, ", ")

JOIN

Joins multiple field or string values together, separated by the given separator. When a field name does not exist in the current dataset, it is treated as a fixed string. Pass an optional final TRUE to skip null values.

JOIN(<field or string value 1>, ..., <field or string value N>, <separator>[, <skip null>])

 select join(First Name, Last Name, " ")

Add TRUE as the last argument to skip nulls, so no extra separators are produced for missing values:

 select join(First Name, Middle Name, Last Name, " ", TRUE)

UPPER

Upper cases a string

UPPER(<field name>)

 select upper(Customer)

LOWER

Lower cases a string

LOWER(<field name>)

 select lower(Customer)

INITCAP

Capitalizes the first letter of each word and lower cases the rest (title case). Word boundaries are any non-alphanumeric characters.

INITCAP(<field name>)

 select initcap(Customer)

PREPEND

Adds a fixed string to the beginning of a field's value.

PREPEND(<field name>, <string value>)

 select prepend(Customer, "Client: ")

APPEND

Adds a fixed string to the end of a field's value. Note: this is the string function, which is different from the append operator used to combine the results of two queries.

APPEND(<field name>, <string value>)

 select append(Customer, " (VIP)")

TRIM

Removes leading and trailing spaces from a string

TRIM(<field name>)

 select trim(address)

LENGTH

Returns the length of a string.

LENGTH(<field name>)

 select length(address)

CURRENCY_FORMAT

Formats a number to a locale specific currency format. Defaults to US currency format (en_US) if locale is not specified.

CURRENCY_FORMAT(<field name>, <locale>)

CURRENCY_FORMAT(<field name>, <decimal points>)

CURRENCY_FORMAT(<field name>, <locale>, <decimal points>)

  select currency_format(revenue)

Example with Locale:

  select currency_format(revenue,en-GBP)

NUMBER_FORMAT

This function allows you to control the display of leading and trailing zeros, prefixes and suffixes, grouping (thousands) separators, and the decimal separator.

NUMBER_FORMAT(<number>,<format>)

select number_format(clicks,##,###.00) as Number of clicks

The output for the preceding lines of code is described in the following table. The value is the number, a double , that is to be formatted. The pattern is the String that specifies the formatting properties. The output, which is a String, represents the formatted number:

NUMBER_FORMAT

This function allows you to control the display of leading and trailing zeros, prefixes and suffixes, grouping (thousands) separators, and the decimal separator.

NUMBER_FORMAT(<number>,<format>)

The output for the preceding lines of code is described in the following table. The value is the number, a double , that is to be formatted. The pattern is the String that specifies the formatting properties. The output, which is a String, represents the formatted number:

REGEX_REPLACE

Replaces each substring of this string that matches the given regular expression with the given replacement.

In case replacement parameter is not provided an empty string value "" is used as default replacement.

REGEX_REPLACE(<field name>, < regex>) REGEX_REPLACE(<field name>, < regex>, < replacement>)

For example, to replace all occurrences of white spaces in a string

  select regex_replace('Morris Park Bake Shop', '\s') as regex_replaced; 
  ==> MorrisParkBakeShop

REGEX_EXTRACT

Extract and return all matches (non-overlapped) for the regular expression from the given input string field.

In case there is no match, NULL will be returned.

REGEX_EXTRACT(<field name>, <regex>, [<extract groups>])

For example, to extract all string occurrences between (and include) '%' characters

  select regex_extract("|Morris Park| |Bake Shop|", "\|([^|]*)\|");
  ==> ["|Morris Park|","|Bake Shop|"]
  select regex_extract("|Morris Park| |Bake Shop|", "\|(([^|]*))\|", true); 
  ==> [ ["|Morris Park|","Morris Park"],["|Bake Shop|","Bake Shop"] ]