SQL Functions

In previous articles, we have seen different SQL commands to get the store and retrieve the data from the database. We have also learned about how to manipulate the data before saving or getting the data. The SQL supports a number of useful functions and in this article, we concentrating on those SQL functions with detailed description with SQL function example & how to write a function in SQL.

What are the SQL Functions?

The SQL Functions are differently built-in special types of commands in SQL. Functions are a one-word command set which returns a single value. Few different functions accept input parameters & few are executed without input parameters. Different database vendors supports a different list of functions Most database vendors are supports to create their own User Define Functions called UDF.

Let’s take a 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 the date the specified format. Another SQL function example 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 inbuilt functions & these functions allow us to carry out different types of manipulations on the data using SQL in function with select query. Each function belongs to any of the following categories and here is the SQL functions list:

• SQL function Strings – these types of functions are only work string data types.
• SQL function date – these types of functions are only working date data types.
• Numeric functions – these types of functions are only working numeric data types.
• Aggregate functions – these types of functions are work on all data types like string, date or numeric data types and help us to get the summarize result sets.
• SQL function sum – these types of functions are work on numeric data types and help us to get the sum result sets.
• SQL function create – these types of functions are used to to create a scalar function, you use the CREATE FUNCTION statement.
• Other functions – SQL server also supports other types of built-in functions & user-defined function in SQL

String Functions:

String functions are functions that are operated on string data types. Let’s see commonly used Sting functions and query examples:

---

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

Oracle Example:

SELECT FirstName || ' ' || LastName AS FullName
FROM Contact;

---

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:

LOWER|UPPER(text)

Explanation here:
Text: The text you want to convert to lowercase/uppercase or a reference to a column that contains the text.

Example LOWER and UPPER Function:

Example 1)

SELECT LOWER('What Are You Doing HERE!'), UPPER('what are you doing HERE!');

Result: what are you doing here!, WHAT ARE YOU DOING HERE!

Example 2)

SELECT UPPER(FirstName) + LOWER(LastName) FROM Contact;

Result: CARSON Williams

---

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:

CONVERT(data_type(length),expression,style)

Explanation here:
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:

SELECT CONVERT(VARCHAR(19),GETDATE())

Result: Aug 12 2013 07:45 AM

SELECT CONVERT(VARCHAR(10),GETDATE(),10)

Result: 08-12-13

SELECT CONVERT(VARCHAR(10),GETDATE(),110)

Result: 08-12-2013

SELECT CONVERT(VARCHAR(11),GETDATE(),6)

Result: 12 Aug 13

SELECT CONVERT(VARCHAR(11),GETDATE(),106)

Result: 12 Aug 2013

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

Result: 12 Aug 2013 07:45:34:243

---

SQL function substr

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)

Explanation here:
Expression: This 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 a number of characters to be returned from the specified string. It gives an error if you specify the negative value as a 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

---

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:

TRIM(text);

Explanation here:
text: The text from which you want spaces removed, or a column that contains the 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?“

---

Numeric functions

The numeric functions are used to perform mathematical calculations on numeric data. Let’s take a look of different arithmetic operators used in the SQL server:

---

Addition Operator (+)

It is used to add values on either side of the operator.

Example of Addition Operator:

Select 20 + 10;

Result: 30

---

Subtraction Operator (-)

It is used to subtract right-hand operand from left-hand operand.

Example of Subtraction Operator:

Select 20 - 10;

Result: 10

---

Multiplication Operator (*)

It is used to multiply values on either side of the operator.

Example of Multiplication Operator:

Select 20 * 10;

Result: 200

---

Division Operator (/)

It is used to divides left-hand operand by right-hand operand.

Example of Division Operator:

Select 20 / 10;

Result: 2

---

Modulus Operator (%)

It is used to divides left-hand operand by right-hand operand and returns the remainder.

Example of Modulus Operator:

Example 1)

Select 20 % 10;

Result: 0

Or

Example 2)

Select 23 MOD 6 ;

Result: 5

---

SQL Functions Summary

• Functions allow 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 with 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 Defined 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 the next article) then let me know in the comments below. I will keep on updating the article for the 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.

---

⇓ Subscribe Us ⇓

---

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Enter your email address:

 

Check email in your inbox for confirmation to get latest updates Software Testing for free.

  Happy Testing!!!