Blog Archive

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
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
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) || '-' || 
FROM table_name

Recommended Reading


  1. great help. thank you for providing this.

  2. There are certain point in our life that we encounter failure but it doesn't mean you will lose hope and give up everything but it only means that every failure there's an exchange and that is new beginning. Well, thank you for sharing your article and keep on posting. Visit my site too for more information.