Version: 8.1

SQL Common Functions

Functions are available in most SQL databases, and can provide some helpful utility to any queries you may be calling in Ignition.

This page contains some commonly used SQL functions that some databases contain. The exact functions available and usage depends on the database, so always check your database's documentation for a more complete list of available functions.

Using Column Values

It is important to understand that when calling these functions, you generally use a column name instead of a static number. For the sake of simplicity, the tables below demonstrate how to use the functions with static values, but they usually are switched for column names, ie:

Pseudocode - Passing a Column to a Function
SELECT SUM(downtime_duration) FROM downtime_events

Example Table

Some of the functions on this table are better demonstrated when used in conjunction with a table (i.e., using the AVG() function with a single value isn't too interesting). Thus, the following table contains sample data that the functions on this page will utilize if necessary.

Products Table

id	product_quantity	product_name	date_added	date_updated	origin_state
1	100	apples	Mon Jan 29 00:00:00 PST 2018	Mon Jan 29 12:00:00 PST 2018	California
2	24	oranges	Mon Feb 13 00:00:00 PST 2017	Mon Feb 13 09:00:00 PST 2017	Florida
3	56	grapes	Mon Mar 07 00:00:00 PST 2016	Mon Mar 07 05:00:00 PST 2016	California

Numeric Functions

Function	Description	Example	Output
ABS(value)	Returns the absolute value of the passed number or column.	`SELECT ABS(-3.5)`	3.5
AVG(value)	Takes the values of a single numeric column, and returns an average. A WHERE clause may be used in the same statement to filter out some of the rows on the table.	`SELECT AVG(product_quantity) FROM products`	60
CEILING(value)	Returns the next greatest integer value based on the argument provided. Thus, CEILING(10.1 ) would return 11.	`SELECT CEILING(10.1)`	11
COUNT(value)	Returns a row count. Typically takes either a single column, *, or 1. Regardless of which row is passed, the function will return the number of rows on the table that meet the criteria of any WHERE clauses.	`SELECT COUNT(*) FROM products`	3
FLOOR(value)	Returns the next smallest integer value based on the argument provided. Thus, FLOOR(10.9) would return 10.	`SELECT FLOOR(10.9)`	10
MAX(value)	Returns the largest value from the specified column.	`SELECT MAX(product_quantity) FROM products`	100
MIN(value)	Returns the smallest value from the specified column.	`SELECT MIN(product_quantity) FROM products`	24
ROUND(value, decimal_places)	Returns a number rounded to a certain number of decimal places. Takes two parameters. The first is the number to round to, and the second is the number of decimal places to round to.	`SELECT ROUND(1.234, 1)`	1.2
SUM(value)	Takes the value of a single numeric column, and returns the sum. A WHERE clause may be used in the same statement to filter out some of the rows on the table.	`SELECT SUM(product_quantity) FROM products`	180

String Functions

Function	Description	Example	Output
CONCAT(value1, value 2,....valueN)	Concatenates multiple strings or values. Some databases may require you to convert each value to a string before concatenating.	`SELECT CONCAT(product_name, ':', product_quantity) FROM products`	apples:100 oranges:24 grapes:56
LOWER(value)	Converts a string to lowercase.	`SELECT LOWER('MAKE Me smALL')`	make me small
LTRIM(value)	Removes leading space from a string.	`SELECTCT LTRIM(' Take a little off the left')`	Take a little off the left
REPLACE(orignal_string, target_string, replacement_string)	Searchings a string for a substring (target_string), and replaces the substring with the replacement_string.	`SELECT REPLACE('Who is awesome', 'Who is', 'You are')`	You are awesome
RTRIM(value)	Removes leading space from a string	`SELECT RTRIM('Take a little off the right ')`	Take a little off the right
SUBSTRING(orignal_string, character_index, [length])	Extracts a substring from another string based on character index. Takes two parameters: the original string, and the character index to start at. An optional third parameter can specify the number of characters to extract. Character index is one-based, so the first character in the string rests at index 1.	`SELECT SUBSTRING('This is my string!' , 9, 9)`	my string
TRIM(value)	Removes both leading and trailing space from a string.	`SELECT TRIM(' Trim Both Sides ')`	Trim Both Sides
UPPER(value)	Converts a string to uppercase.	`SELECT UPPER('super size me')`	SUPER SIZE ME

Date Functions

There are many date and time functions for each database (MySQL, MSSQL, Oracle, etc), but they all vary wildly. These examples work in most databases:

Function	Description	Example	Output
CURRENT_TIMESTAMP()	Returns the current date and time, as reported by the database.	`SELECT CURRENT_TIMESTAMP()`	Returns the current time
TIMEDIFF(date1, date2)	Returns a difference between two dates. Assumes thate date1 is the most recent datetime.	`SELECT TIMEDIFF(date_updated, date_added) FROM products WHERE id = 1`	Thu Jan 01 12:00:00 PST 1970

Logic Functions

Function Description Example Output

COALESCE(value1, value2,...valueN)

Returns the first non-null expression.

Function	Description	Example	Output
COALESCE(value1, value2,...valueN)	Returns the first non-null expression.	`SELECT COALESCE(NULL, 'Pick me!')`	Pick me!
ISNULL(expression)	Returns true if an expression is NULL.	`SELECT ISNULL(NULL)` `SELECT ISNULL(14)`	True False
NULLIF(expression1, expression2)	Compares two expressions. If they are equal to each other, then the function returns a NULL. If the two expressions are not equal, the first expression passed to NULLIF() is returned.	`SELECT NULLIF(100, 100)` `SELECT NULLIF(100, 3)`	NULL 100

SELECT
    COALESCE(NULL, 'Pick me!')

Pick me!

ISNULL(expression)

Returns true if an expression is NULL.

SELECT
    ISNULL(NULL)

SELECT
    ISNULL(14)

True

False

NULLIF(expression1, expression2)

Compares two expressions. If they are equal to each other, then the function returns a NULL. If the two expressions are not equal, the first expression passed to NULLIF() is returned.

SELECT
    NULLIF(100, 100)

SELECT
    NULLIF(100, 3)

NULL

100

Group By Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. This way you can find the MIN, MAX, Average, COUNT, etc., for each group of rows. Grouping can also be done for multiple columns, with precedence going in the order that they are listed.

Pseudocode - Passing a Column to a Function
SELECT SUM(column1) FROM table GROUP BY column2

Example

Here, we are grabbing the sum of product quantity for each origin state.

SQL - Simple Group By
SELECT SUM(product_quantity), origin_state, FROM products_table GROUP BY origin_state

Using Column Values​

Example Table​

Products Table​

Numeric Functions​

String Functions​

Date Functions​

Logic Functions​

Group By Clause​

Example​