Skip to main content
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

idproduct_quantityproduct_namedate_addeddate_updatedorigin_state
1100applesMon Jan 29 00:00:00 PST 2018Mon Jan 29 12:00:00 PST 2018California
224orangesMon Feb 13 00:00:00 PST 2017Mon Feb 13 09:00:00 PST 2017Florida
356grapesMon Mar 07 00:00:00 PST 2016Mon Mar 07 05:00:00 PST 2016California

Numeric Functions

FunctionDescriptionExampleOutput
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

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

FunctionDescriptionExampleOutput
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

FunctionDescriptionExampleOutput
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

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