In previous articles we have seen about different SQL commands to get store and retrieve the data from database. We have also learned about how to manipulate the data before saving or getting the data. The SQL supports number of useful functions and in this article we concentrating on those SQL functions with detailed description with Sql function examples & how to write function in sql.
What are Functions?
The SQL Functions are different built in special type of commands in SQL. Functions are one-word command set which return single value. Few different functions accept input parameters & few are executed without input parameters. Different database vender’s supports different list of functions Most database venders are supports to create their own User Define Functions called UDF.
Let’s take simple example of Function in SQL Server. The SQL saves the date in the format of “YYYY-MM-DD” & in the application user want to get the date in “DD-MM-YYYY” format then we can use DATE_FORMAT function to get in date the specified format. Other example of SQL function is CURRRENT_TIMESTAMP which is used to get the current date time. These are the most commonly used functions when date time comes in the picture.
Different types of SQL functions:
SQL server supports plenty of in built functions & these functions allow us to carry out different types of manipulations on the data. Each function belongs to any of the following category:
- Strings functions – these types of functions are only work string data types.
- Date functions – these types of functions are only work date data types.
- Numeric functions – these types of functions are only work numeric data types.
- Aggregate functions – these types of functions are work on all data type like string, date or numeric data types and help us to get the summarize result sets.
- Other functions – SQL server also supports other types of built in functions & user defined function in sql
String functions are functions which are operated on string data types. Let’s see commonly used Sting functions and query examples:
1) CONCATENATE Function:
The CONCATENATE function is used to join two different strings. Using more than two strings it produces a single output string value. Each database provides a way to do this:
Syntax of CONCATENATE Function:
• SQL Server: +
• MySQL: CONCAT( )
• Oracle: CONCAT( ), ||
SQL Server Example:
SELECT FirstName + ' ' + LastName AS FullName FROM Contact
MSSQL Server Example:
SELECT CONCAT(FirstName, LastName) AS FullName FROM Contact
SELECT FirstName || ' ' || LastName AS FullName FROM Contact;
2) LOWER and UPPER Function:
The functions LOWER and UPPER are used to convert the case of the string. These functions are supported in all databases.
Syntax of LOWER and UPPER Function:
Text: The text you want to convert to lowercase/uppercase, or a reference to a column that contains text.
Example LOWER and UPPER Function:
SELECT LOWER('What Are You Doing HERE!'), UPPER('what are you doing HERE!');
Result: what are you doing here!, WHAT ARE YOU DOING HERE!
SELECT UPPER(FirstName) + LOWER(LastName) FROM Contact
Result: CARSON wiilams
3) CONVERT Function:
The CONVERT() function is a general function that converts an expression of one data type to another. It is also used to display date/time data in different formats.
Syntax of CONVERT Function:
data_type(length): Specifies the target data type (with an optional length)
expression: Specifies the value to be converted style Specifies the output
format: for the date/time.
Example of CONVERT Function:
Result: Aug 12 2013 07:45 AM
Result: 12 Aug 13
Result: 12 Aug 2013
Result: 12 Aug 2013 07:45:34:243
4) SUBSTRING Function:
The SUBSTRING() function is used to extract a character string from a given starting position for a given length.
Syntax of SUBSTRING Function:
SUBSTRING (expression, start, length)
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 of starting position from where part of a string begins.
Length: Is number of characters to be returned from specified string. It gives error if you specify negative value as length.
Note: If any of the inputs are NULL, the SUBSTRING function returns a NULL.
SUBSTRING Function Examples:
SELECT FirstName, SUBSTRING(FirstName, 1, 1) FROM Contact WHERE FirstName = 'Carson'
Result: Carson C
5) TRIM Function:
The TRIM function is used to removes all spaces from text except for single spaces between words. This function also removes other types of characters from a specified character string. The default function is to trim the specified character from both sides of the character string.
Syntax of TRIM Function:
text: The text from which you want spaces removed, or a column that contains text.
If you want to remove the leading or trailing spaces then you can use SQL server LTRIM and RTRIM functions respectively. If no removal string is specified, TRIM removes spaces by default. These functions cannot remove other types of characters.
Examples of TRIM Function:
SELECT TRIM(' What are you doing? ');
Result: “What are you doing?”
SELECT LTRIM(' What are you doing? ');
Result: “What are you doing? ”
SELECT RTRIM(' What are you doing? ');
Result:“ What are you doing?“
The numeric functions are used perform mathematical calculations on numeric data. Let’s take a look of different arithmetic operators used in the SQL server:
1) Addition Operator (+):
It is used to add values on either side of the operator.
Example of Addition Operator:
Select 20 + 10;
2) Subtraction Operator (-):
It is used to subtract right hand operand from left hand operand.
Example of Subtraction Operator:
Select 20 - 10;
3) Multiplication Operator (*):
It is used to multiply values on either side of the operator.
Example of Multiplication Operator:
Select 20 * 10;
4) Division Operator (/):
It is used to divides left hand operand by right hand operand.
Example of Division Operator:
Select 20 / 10;
5) Modulus Operator (%):
It is used to divides left hand operand by right hand operand and returns remainder.
Example of Modulus Operator:
Select 20 % 10;
Select 23 MOD 6 ;
SQL Functions Summary:
- Functions are allows you to reach great heights in maintainability and modularity.
- Using functions to accomplish specific tasks improves the reliability of other modules and reduces development time.
- Functions are helping you for the performance improvement.
- Some built in functions comes with database & these are categorized based on the data types like Sting, Numeric, Date and Aggregate functions etc.
- SQL server also supports User Define Functions.
I think I have addressed all major types of SQL Functions used in day to day life. I have worked for around 3 years out of my testing career on Database / ETL testing. There are some experts who have spent their whole career life on Database / ETL testing. If I missed out addressing some important points in SQL functions (except Aggregate Function, we will see this in next article) then let me know in comments below. I will keep on updating the article for latest testing information.
If you like this article you would also like to subscribe to our software testing email newsletter for software testing latest updates.