SQL Functions

SQL Functions, Structured Query Language Fundamentals, Aggregate Functions, String Functions, Date & Time Functions, and SQL Scalar functions.

SQL Built-in Functions

A function is a database object in Sql Server. Basically, it is a set of SQL statements that accepts only input parameters, performs actions and returns the result. The function can return only a single value or a table. We can’t use functions to Insert, Update, and Delete records in the database table.

SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions:

The SQL Aggregate Functions are functions that provide mathematical operations. Aggregate functions operate on a collection of values and return a single value. The information in multiple records are processed in a particular manner and then displayed in a single record answer. Aggregate functions are often used in conjunction with GROUP BY clause and cannot be nested. The expression cannot be a sub query.

The functions include:

• count() – counts a number of rows

• sum() – compute sum

• avg() – compute average

• min() – compute minimum

• max() – compute maximum

AVG: AVG returns the average of the values in the expression. The expression must contain numeric values. Null values are ignored.

Syntax: AVG ([ALL | DISTINCT] <expression>)

Parameters:

ALL: Applies to all values.

DISTINCT: Return the sum of unique values.

Expression: Expression made up of a single constant, variable, scalar function, or column name. Expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example: SELECT AVG (`amount_paid`) FROM ‘payments’;

It returns the average value of the paid amount from payments table.

COUNT: Returns the number of items in expression. The data type returned is of type int.

Syntax: COUNT ([ALL | DISTINCT] <expression> | *)

Example: Select COUNT (Supplier_ID) FROM ‘suppliers’;

It returns the number of items in supplier id from suppliers table.

SUM: Returns the total of all values in expression. Sum ignores any NULL values.

Syntax: SUM ([ALL | DISTINCT] <expression>)

Example: SELECT SUM (advance_amount) FROM ‘orders’;

It returns the sum of amount from the orders table

MIN: Returns the smallest value from expression. Min ignores any NULL values.

Syntax: MIN ([ALL | DISTINCT] <expression>)

Example: SELECT MIN (ord_amount) FROM ‘orders’;

It returns the minimum order amount value from the orders table.

MAX: Returns the maximum value from expression. Max ignores any NULL values.

Syntax: MAX ([ALL | DISTINCT] <expression>)

Example: SELECT MAX (ord_amount) FROM orders;

It returns the maximum amount value from the orders table.


SQL String Functions:

Sql string function is a built-in string function. It performs an operation on a string input value and returns a string or numeric value.

ASCII: ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Syntax: ASCII (character_expression)

Arguments:

character_expression: Is an expression of the type char or varchar.

CHAR: It is a string function that converts an int ASCII code to a character.

Syntax: CHAR (integer_expression)

Arguments:

integer_expression: Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

CHARINDEX: It is a string function that takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Syntax: CHARINDEX (expression1, expression2 [, start_location])

Arguments:

expression1: Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2: Is an expression, usually, a column searched for the specified sequence. expression2 is of the character string data type category.

start_location: Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

DIFFERENCE: Returns the difference between the SOUNDEX values of two character expressions as an integer.

Syntax: DIFFERENCE (character_expression, character_expression)

Arguments:

character_expression: Is an expression of type char or varchar. character_expression can also be of type text; however, only the first 8,000 bytes are significant.

LEFT: It is a string function that takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Syntax: LEFT (character_expression, integer_expression)

Arguments:

character_expression: Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type (except text or ntext) that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression: Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned.

RIGHT: It is a string function that takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Syntax: RIGHT (character_expression, integer_expression)

Arguments:

character_expression: Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type (except text or ntext) that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression: Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned.

LOWER: It is a string function that returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Syntax: LOWER (character_expression)

Arguments:

character_expression: Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

UPPER: It is a string function that returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Syntax: UPPER (character_expression)

Arguments:

character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

PATINDEX: It is a function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments. 1st argument as string value specifying the pattern to match. 2nd argument as string value specifying the string to compare.

Syntax: PATINDEX (‘%pattern%’, expression)

Arguments:

pattern: Is a literal string. Wildcard characters can be used; however, the % character must precede and follow pattern (except when searching for first or last characters). pattern is an expression of the short character data type category.

expression: Is an expression, usually a column that is searched for the specified pattern. expression is of the character string data type category.

LTRIM: It is a function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Syntax: LTRIM (character_expression)

Arguments:

character_expression: Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

RTRIM: It is a function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Syntax: RTRIM (character_expression)

Arguments:

character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

REVERSE: It is a string function returns the string in reverse order. It takes 1 argument as string value.

Syntax: REVERSE (character_expression)

Arguments:

character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

REPLICATE: Repeats a character expression for a specified number of times.

Syntax: REPLICATE (character_expression, integer_expression)

Arguments:

character_expression: Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

integer_expression: Is a positive whole number. If integer_expression is negative, a null string is returned.

SOUNDEX: Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

Syntax: SOUNDEX (character_expression)

Arguments:

character_expression: Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

SPACE: Returns a string of repeated spaces.

Syntax: SPACE (integer_expression)

Arguments:

integer_expression: Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

STR: Returns character data converted from numeric data.

Syntax: STR (float_expression [, length [, decimal]])

Arguments:

float_expression: Is an expression of approximate numeric (float) data type with a decimal point.

length: Is the total length, including decimal point, sign, digits, and spaces. The default is 10.

decimal: Is the number of places to the right of the decimal point.

STUFF: Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax: STUFF (character_expression, start, length, character_expression)

Arguments:

character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start: Is an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.

length: Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

SUBSTRING: Returns part of a character, binary, text, or image expression.

Syntax: SUBSTRING (expression, start, length)

Arguments:

expression: Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start: Is an integer that specifies where the substring begins.

length: Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.


SQL Date Functions:

Date functions operate on date data types. Different types of date functions are,

DATEADD: Returns the date that result from adding the specified number of datepart units to the date.

Syntax: DATEADD (datepart, number, date)

Example: SELECT DATEADD (day, 21, getdate ())

DATEDIFF: Returns the number of datepart units between the specified start date and end date.

Syntax: DATEDIFF (datepart, startdate, enddate)

Example: SELECT DATEDIFF (day, ’03/03/2013′, getdate ())

DATEPART: Is the parameter that specifies the part of the date to return? The table lists date parts and abbreviations recognized by Microsoft SQL Server.

Syntax: DATEPART (datepart, date)

Example:

SELECT GETDATE () AS ‘Current Date’

SELECT DATEPART (month, GETDATE ()) AS ‘Month Number’

SELECT DATEPART (m, 0), DATEPART (d, 0), DATEPART (yy, 0)

DATENAME: Returns an integer representing the specified date part of the specified date.

Syntax: DATENAME (datepart, date)

Example: SELECT DATENAME (month, getdate ()) AS ‘Month Name’

GETDATE: Returns the current system date and time.

Syntax: GETDATE ()

Example: SELECT GETDATE ()


SQL Tutorial for Beginners
SQL Full Course Video
SQL Syllabus
SQL Fundamentals Quiz
SQL Queries for Software Testers
Follow me on social media: