Programmers Sample Guide: SQL built in function syntax cheat sheet and examples

SQL built-in functions cheat sheet is very helpful. You can use these for any embedded SQL programming language such as SQLRPGLE on iSeries(AS400), for SQL in java programs using JDBC connections. You can also query files on iSeries using STRSQL command which provides a SQL command line.

SQL AVG() Function Syntax

The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name) FROM table_name

SQL COUNT() Function Syntax

The COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(*) FROM table_name
SELECT COUNT(column_name) FROM table_name

The COUNT(DISTINCT column_name) function returns the number of distinct values for the specified column

SELECT COUNT(DISTINCT column_name) FROM table_name

SQL MAX() Function Syntax

The MAX() function returns the largest value for the selected column.

SELECT MAX(column_name) FROM table_name

SQL MIN() Function Syntax

The MIN() function returns the smallest value for the selected column.

SELECT MIN(column_name) FROM table_name

SQL SUM() Function Syntax

The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name

SQL UCASE() or UPPER() Function Syntax (Depends on System)

The UCASE() or UPPER() function converts the value of a selected column to uppercase.

SELECT UCASE(column_name) FROM table_name
OR
SELECT UPPER(column_name) FROM table_name

SQL LCASE() or LOWER() Function Syntax (Depends on System)

The LCASE() or LOWER() function converts the value of a selected column to lowercase.

SELECT LCASE(column_name) FROM table_name
OR
SELECT LOWER(column_name) FROM table_name

SQL SUBSTRING() Function Syntax

The SUBSTRING() function is used to extract data from text fields

SELECT SUBSTRING(column_name,start[,length]) FROM table_name

SQL ROUND() Function Syntax

The ROUND() function is used to round a numeric field to the number of decimals specified

SELECT ROUND(column_name,decimals) FROM table_name

SQL DIGITS() Function Syntax

The DIGITS() function is used to convert character data from numeric

SELECT DIGITS(column_name) FROM table_name

SQL DAYOFWEEK() Function Syntax

The DAYOFWEEK function returns an integer in the range of 1 to 7 that represents the day of the week where 1 is Sunday and 7 is Saturday.

SELECT dayofweek( 
substring(digits(numeric_date),1,4) || '-' || 
substring(digits(numeric_date),5,2) || '-' || 
substring(digits(numeric_date),7,2)) 
FROM table_name

Programmers Sample Guide

SQL built in function syntax cheat sheet and examples