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)