If you are a SQL user, you know that functions are essential to this programming language.
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.
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:
SELECTCast(10ASVARCHAR);
Output:
'10'
2. Coalesce Function: COALESCE()
The coalesce function returns the first non-null value from a list of expressions.
Coalesce Function Example:
SELECTCOALESCE(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:
SELECTIsnull('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:
SELECTIfnull('value','null');
Output:
'value'
5. NVL Function: NVL()
The NVL function replaces null values with a specified default value.
NVL Function Example:
SELECTNvl(NULL,'default value');
Output:
'default value'
6. Case Expression: CASE
The case expression performs conditional operations based on a specified expression.
Case Expression Example:
SELECTCASE
WHEN1=1THEN'true'
ELSE'false'
END;
Output:
'true'
7. Convert Function: CONVERT()
The convert function converts a value into a specified data type.
Convert Function Example:
SELECTCONVERT(VARCHAR,10);
Output:
'10'
8. Split Function: STRING_SPLIT()
The split function splits a string into a table of substrings.
Split Function Example:
SELECT*
FROMString_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:
SELECTRand();
Output:
0.7750247322012854
10. If Function: IF()
The if function performs a conditional operation based on a specified expression.
If Function Example:
SELECTIF(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:
SELECTMid('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:
SELECTDateadd(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:
SELECTDate_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:
SELECTDatepart(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:
SELECTDayofweek('2023-05-05');
Output:
5
16. Week Function: WEEK()
The week function returns the week number for a given date.
Week Function Example:
SELECTWeek('2023-05-05');
Output:
18
17. Weekday Function: WEEKDAY()
The weekday function returns the weekday index for a given date.
Weekday Function Example:
SELECTWeekday('2023-05-05');
Output:
2
18. Year Function: YEAR()
The year function extracts the year from a date value.
Year Function Example:
SELECTYear('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:
SELECTLen('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:
SELECTLEFT('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:
SELECTRIGHT('banana',3);
Output:
'ana'
22. Trim Function: TRIM()
The trim function removes leading and trailing spaces from a string.
Trim Function Example:
SELECTTrim(' banana ');
Output:
'banana'
23. Ascii Function: ASCII()
The ascii function returns the ASCII code value for a specified character.
Ascii Function Example:
SELECTAscii('A');
Output:
65
24. Concatenate Function: CONCAT()
The concatenate function links two or more strings together.
Concat Function Example:
SELECTConcat('apple','banana','cherry');
Output:
'applebananacherry'
25. Format Function: FORMAT()
The format function reformats a value into a specified string format.
Format Function Example:
SELECTFormat(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:
SELECTReplace('banana','a','e');
Output:
Benene
27. Substring Function: SUBSTRING()
The SUBSTRING function extracts a substring from a given string.
Substring Function Example:
SELECTSubstring('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:
SELECTInstr('banana','a');
Output:
2
29. Stuff Function: STUFF()
The stuff function replaces a substring within a string with another substring.
Stuff Function Example:
SELECTStuff('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.
