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

Recommended Reading


1 comment :

  1. great help. thank you for providing this.

    www.n8fan.net

    ReplyDelete