String & Number Operators

ROUND

Specify the number of decimal points to display

ROUND(<field>, <decimal points>)

select round(sent,1)

 

PERCENTILE

Returns the value of the field for the specified percentile rank.

PERCENTILE(<field>, <percentile>)

select percentile(sent,75) 

 

CURRENCY_FORMAT

Formats a number to a locale-specific currency format. Defaults to US currency format (en_US) if the 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)

 

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_before(Message Type,someDelim)

 

CONCAT

It 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, ", ")

 

REGEX_REPLACE

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

In case the replacement parameter is not provided an empty string value "" is used as a 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"] ]

 

ARRAY_TO_STRING

Join elements of an array value together separated by a 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, ", ")

 

UPPER & LOWER CASE

Upper cases a string

       UPPER(<field name>)

 select upper(Customer)

Lower cases a string

      LOWER(<field name>)

 select lower(Customer)

 

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>)

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:

Value	Pattern	Output	Explanation
123456.789	###,###.###	123,456.789	The pound sign (#) denotes a digit, the comma is a placeholder for the grouping separator, and the period is a placeholder for the decimal separator.
123456.789	###.##	123456.79	The value has three digits to the right of the decimal point, but the pattern has only two. The format method handles this by rounding up.
123.78	000000.000	000123.780	The pattern specifies leading and trailing zeros, because the 0 character is used instead of the pound sign (#).
12345.67	$###,###.###	$12,345.67	The first character in the pattern is the dollar sign ($). Note that it immediately precedes the leftmost digit in the formatted output.
12345.67	\u00A5###,###.###	¥12,345.67	The pattern specifies the currency sign for Japanese yen (¥) with the Unicode value 00A5.