Functions
Updated over a week ago

10 Most Common Functions

Case When... Returns value(s) that meets the criteria of the Case statement

Coalesce Returns the first non-null value in a series of like arguments

Concat Joins text end-to-end

Date_Diff Returns number of periods between two dates for given interval

Now Returns the current local datetime

Substring Extracts part of a text

To_Date Converts a value to a date


Type Casting

Hash Converts a value to a hashed value using sha1 or md5 algorithms

To_Date Converts a value to a date

To_Datetime Converts a value to a datetime

Supported value types include: Number, date and datetime.

To_Integer Converts a value to an integer

To_Number Converts a value to a number

To_Text Converts a value to text

[Optional] Date and datetimes can be specially formatted

Date format options:

  • %Y-%m

  • %m-%d

  • %m-%d-%Y

  • %Y-%m-%d

Datetime format options:

  • %Y-%m-%d %H:%M:%S

  • %m/%d/%Y %H:%M:%S

Numeric format options:

  • TO_TEXT(numeric, "05.3f") means format the numeric value into 3 decimals, at least 5 characters in length, with 0 paddings if needed.


Date & Time Operators

Date Creates date from numeric date parts

Datetime Creates datetime from datetime parts

Date_Diff Returns number of periods between two dates for given interval

Day Extracts the day part from date or datetime

Day_Of_Week Extracts the day of week from a date or datetime.

The week starts on Monday with value 1.

Day_Of_Year Extracts the day of year from a date or datetime

Days_In_Month Returns the number of days in the month from a date or datetime

Values range from 28 to 31.

Days_In_Year Returns the number of days in the year from a date or datetime.

During_Daylight_Saving Returns 0 for daylight saving and 1 for standard time.

During_Standard Returns 0 for standard and -1 for daylight saving.

Hour Extracts the hour part from datetime

Minute Extracts the minute part from datetime

Month Extracts the month part from date or datetime

Now Returns the current local datetime

Quarter Returns the quarter of the year of a date or datetime

Second Extracts the second part from datetime

Time_Period Returns standard period name for given date or datetime field

Time_Period_Offset Returns number of periods from current period for given interval

Today Returns the current UTC date

Week Extracts the week of year from a date or datetime

Year Extracts the year part from date or datetime


Text Operators

Concat Joins text end-to-end

Is_Empty Returns TRUE is text has no characters or is null

Left_Substr Extracts all text length X from the left of a text string

Length Returns the number of characters in text

Levenshtein Returns the Levenshtein distance between two input strings

Lower Converts all characters to lower-case

Proper Converts word's 1st character to upper-case, every other lower-case

REGEX_Match Determines if a given string matches a specified regular expression pattern

REGEX_Replace Finds all occurrences of a specified regular expression pattern in a string and replace them with a new specified value

REGEX_Search Searches a given string for the first occurrence of a specified regular expression pattern and returns index of substring of first occurrence

Replace Replaces all instances of old text with new text

Reverse Reverses the order of text

Right_Substr Extracts all text length X from the right of a text string

Soundex Returns the four-character Soundex code for input text

Split_Part Splits text every instance of another text, returns part number text

Part number values start at 1. Returns null if no split is found.

Str_Pos Returns the position of the first instance of search text

Position values start at 1. Returns -1 if search text is not found.

Substring Extracts part of a text

Trim Removes leading and trailing white space from text

Upper Converts all characters to upper-case


Number Operators

Abs Returns the absolute value of the number input

Ceil Returns the smallest integer greater than or equal to number input

Floor Returns the greatest integer less than or equal to number input

MOD Calculates the remainder when dividing one number (dividend) by another (divisor)

Log Returns the log of a value with a given base number

Power Returns the value of a number to a given exponent

Random Returns a random number between 0 and 1

Round Returns the number input rounded to the nearest integer


Other

Case When... Returns value(s) that meets the criteria of the Case statement

Coalesce Returns the first non-null value in a series of like arguments

Escape_HTML

IF Conditional control that enables returning different values based on the evaluation of a specified condition.

In Returns value(s) that meet the criteria of the IN statement

JSON_Field Extracts field value pair from JSON String

JSON_Set Creates JSON string with field value pairs

Can be nested to generate a JSON with multiple fields.

Unescape_HTML


Did this answer your question?