SQL Server Functions: 29 Examples + Definitions

Clint Fontanella
Clint Fontanella

Published:

If you are a SQL user, you know that functions are essential to this programming language.

Developer using a list of SQL server functions

Functions manipulate data, making it easier to extract valuable insights from ever-growing databases. However, with so many functions available for use, it can be challenging to know which ones to leverage and when. In this post, we'll explore the most common SQL Server functions, along with examples to help you understand how they work and how to use them in your SQL queries.

Whether you are a beginner or a seasoned SQL user, this guide will help you expand your knowledge and enhance your data analysis capabilities.

Get Started with HubSpot's Analytics Software for Free

Table of Contents

What are SQL server functions?

SQL server functions are pre-built actions that perform calculations, manipulate data, and return results. At the most fundamental level, these functions simplify complex queries and automate repetitive tasks.

Common SQL functions include string, numeric, date/time, conditional, and aggregate functions. These functions give users the ability to extract and analyze data from massive databases efficiently and effectively.

SQL Server Functions

1. Cast Function: CAST()

The cast function converts a data type into another data type.

Cast Function Example:

SELECT Cast(10 AS VARCHAR); 

Output:

'10'

2. Coalesce Function: COALESCE()

The coalesce function returns the first non-null value from a list of expressions.

Coalesce Function Example:

SELECT COALESCE(NULL, 'value2', 'value3'); 

Output:

'value2'

3. Is Null Function: ISNULL()

The is null function checks if an expression is null; if it is, it returns a replacement value.

Is Null Function Example:

SELECT Isnull('value', 'null'); 

Output:

'value'

4. If Null Function: IFNULL()

The if null function is similar to the is null function, but the arguments are reversed.

If Null Function Example:

SELECT Ifnull('value', 'null'); 

Output:

'value'

5. NVL Function: NVL()

The NVL function replaces null values with a specified default value.

NVL Function Example:

SELECT Nvl(NULL, 'default value'); 

Output:

'default value'

6. Case Expression: CASE

The case expression performs conditional operations based on a specified expression.

Case Expression Example:

SELECT CASE
         WHEN 1 = 1 THEN 'true'
         ELSE 'false'
       END; 

Output:

'true'

7. Convert Function: CONVERT()

The convert function converts a value into a specified data type.

Convert Function Example:

SELECT CONVERT(VARCHAR, 10); 

Output:

'10'

8. Split Function: STRING_SPLIT()

The split function splits a string into a table of substrings.

Split Function Example:

SELECT *
FROM   String_split('apple,banana,cherry', ','); 

Output:

apple banana cherry

9. Random Function: RAND()

The random function generates a random number between 0 and 1.

Random Function Example:

SELECT Rand(); 

Output:

0.7750247322012854

10. If Function: IF()

The if function performs a conditional operation based on a specified expression.

If Function Example:

SELECT IF(1 = 1, 'true', 'false'); 

Output:

'True'

11. Mid Function: MID()

The mid function extracts a substring from a string, starting at a specified position and with a specified length.

Mid Function Example:

SELECT Mid('apple', 2, 3); 

Output: 

'ppl'

SQL Date Functions

12. Date Add Function: DATEADD()

The date add function adds a specified number of intervals (such as days, months, or years) to a date.

Date Add Function Example:

SELECT Dateadd(day, 7, '2023-05-05'); 

Output:

'2023-05-12'

13. Date Format Function: DATE_FORMAT()

The date format function reformats a date value into a string with a specified format.

Date Format Example:

SELECT Date_format('2023-05-05', '%m/%d/%Y'); 

Output:

'05/05/2023'

14. Date Part Function: DATEPART()

The date part function extracts a specified part of a date, such as year, month, or day.

Date Part Example:

SELECT Datepart(year, '2023-05-05'); 

Output:

2023

15. Day of the Week Function: DAYOFWEEK()

The day of the week function returns the day of the week for a given date.

Day of the Week Example:

SELECT Dayofweek('2023-05-05'); 

Output:

5

16. Week Function: WEEK()

The week function returns the week number for a given date.

Week Function Example:

SELECT Week('2023-05-05'); 

Output:

18

17. Weekday Function: WEEKDAY()

The weekday function returns the weekday index for a given date.

Weekday Function Example:

SELECT Weekday('2023-05-05'); 

Output:

2

18. Year Function: YEAR()

The year function extracts the year from a date value.

Year Function Example:

SELECT Year('2023-05-05'); 

Output:

2023

SQL String Functions

19. Length Function: LEN()

The length function is used to return the length of a string.

Length Function Example:

SELECT Len('banana'); 

Output:

6

20. Left Function: LEFT()

The left function extracts a specified number of characters from the beginning of a string.

Left Function Example:

SELECT LEFT('banana', 3); 

Output:

'ban'

21. Right Function: RIGHT()

The right function extracts a specified number of characters from the end of a string.

Right Function Example:

SELECT RIGHT('banana', 3); 

Output:

'ana'

22. Trim Function: TRIM()

The trim function removes leading and trailing spaces from a string.

Trim Function Example:

SELECT Trim(' banana ');

Output:

'banana'

23. Ascii Function: ASCII()

The ascii function returns the ASCII code value for a specified character.

Ascii Function Example:

SELECT Ascii('A'); 

Output:

65

24. Concatenate Function: CONCAT()

The concatenate function links two or more strings together.

Concat Function Example:

SELECT Concat('apple', 'banana', 'cherry'); 

Output: 

'applebananacherry'

25. Format Function: FORMAT()

The format function reformats a value into a specified string format.

Format Function Example:

SELECT Format(12345.6789, '#,##0.00'); 

Output:

12,345.68

26. Replace Function: REPLACE()

The replace function replaces a specified string with another string in a given expression.

Replace Function Example:

SELECT Replace('banana', 'a', 'e'); 

Output:

Benene

27. Substring Function: SUBSTRING()

The SUBSTRING function extracts a substring from a given string.

Substring Function Example:

SELECT Substring('banana', 2, 3); 

Output:

'man'

28. In String Function: INSTR()

The in string function finds the position of a specified substring within a string.

In String Function Example:

SELECT Instr('banana', 'a'); 

Output:

2

29. Stuff Function: STUFF()

The stuff function replaces a substring within a string with another substring.

Stuff Function Example:

SELECT Stuff('banana', 2, 3, 'oro'); 

Output:

'borona'

Looking for more SQL functions? Check out these additional guides:

Using SQL Server Functions

SQL server functions are powerful tools that can make data management and analysis more efficient and effective. With a wide range of functions available, you can easily manipulate and transform data to get the insights you need. The examples and definitions we’ve provided here should give you a good understanding of some of the most commonly used SQL server functions and inspire you to explore more advanced functions as you become more comfortable with SQL.

Author's Note: This post was written/edited by a human with the assistance of generative AI.

analytics

Related Articles

Get Started with HubSpot's Analytics Software for Free

GET STARTED FREE

CMS Hub is flexible for marketers, powerful for developers, and gives customers a personalized, secure experience

START FREE OR GET A DEMO