String & Currency Operators

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)

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

 select upper(Customer)

Lower cases a string

 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)