SQL Server Functions
Learn via video course
FREE
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Overview
SQL Server offers a wide range of functions designed to meet different data manipulation requirements. The purpose of these functions is to make complex SQL queries simpler and to automate repetitive tasks. We will learn more about these functions in this article.
What are SQL Server Functions?
SQL Server functions are built-in functions designed for various predefined tasks, including calculations, data management, and results retrieval. They play a crucial role in streamlining recurring processes and making complicated queries simpler ensuring efficient database operations. They are mainly categorized into strings, numbers, dates/times, conditions, and aggregates.
SQL Server String Functions
| SQL Server Function | Description |
|---|---|
| ASCII | This function obtains the ASCII value for a given character. |
| CHAR | This function fetches the character based on the ASCII code. |
| CHARINDEX | This function locates the position of a substring within a string. |
| CONCAT | This function merges two or more strings. |
| Concat with + | This function is also used to merge two or more strings. |
| CONCAT_WS | This function merges two or more strings with a specified separator. |
| DATALENGTH | This function counts the number of bytes used to represent an expression. |
| DIFFERENCE | This function compares two SOUNDEX values returning an integer value as the output. |
| FORMAT | This function helps in formatting a value according to the given format. |
| LEFT | This function returns a set number of characters from the left side of a string. |
| LEN | This function gives the length of a string as an output. |
| LOWER | This function updates every character of a string to lowercase. |
| LTRIM | This function trims the leading or front spaces from a string. |
| NCHAR | This function retrieves the Unicode character based on a numerical code. |
| PATINDEX | This function returns the position of a pattern in a string |
| QUOTENAME | This function produces a Unicode string with added delimiters for SQL Server identifiers. |
| REPLACE | This function substitutes all occurrences of a substring with a new substring. |
| REPLICATE | This function duplicates a string a given number of times. |
| REVERSE | This function inverts a string and provides the result. |
| RIGHT | This function fetches a defined number of characters from the right side of a string and returns them as an output. |
| RTRIM | This function removes trailing or ending spaces from a string. |
| SOUNDEX | This function generates a four-character code to assess the similarity of two strings. |
| SPACE | This function produces a string with a specified number of space characters. |
| STR | This function updates a number value to a string value. |
| STUFF | This function deletes a section of a string and inserts another part at a designated position. |
| SUBSTRING | This function fetches some particular characters from a given string. |
| TRANSLATE | This function yields a string after translating characters from the second argument into those specified in the third argument. |
| TRIM | This function trims or eliminates leading and trailing spaces or other specified characters from a string. |
| UNICODE | This function retrieves the Unicode value for the first character of the input expression. |
| UPPER | This function changes all the characters of a string to uppercase. |
SQL Server Numeric Functions
| SQL Server Function | Description |
|---|---|
| ABS | This function gives the positive value of a number. |
| ACOS | This function provides the angle whose cosine is a specified number. |
| ASIN | This function gives the angle whose sine is a specified number. |
| ATAN | This function provides the angle whose tangent is a specified number. |
| ATN2 | This function gives the angle whose tangent is the quotient of two specified numbers. |
| AVG | This function calculates the average value of a set of numbers. |
| CEILING | This function makes a number go up to the nearest whole number. |
| COUNT | This function determines the number of records returned by a select query. |
| COS | This function gives the cosine of a number. |
| COT | This function provides the cotangent of a number. |
| DEGREES | This function changes a value from radians to degrees. |
| EXP | This function gives the value of 'e' raised to the power of a specified number. |
| FLOOR | This function makes a number go down to the nearest whole number. |
| LOG | This function gives the natural logarithm of a number or the logarithm of a number to a specified base. |
| LOG10 | This function provides the base 10 logarithm of a number. |
| MAX | This function retrieves the maximum value among a set of values. |
| MIN | This function retrieves the minimum value among a set of values. |
| PI | This function provides us with the PI value. |
| POWER | This function raises a specified number to the power of another number. |
| RADIANS | This function changes a degree value into radians. |
| RAND | This function creates a random number and gives that as output. |
| ROUND | This function rounds a number to a specified number of decimal places and returns that as an output. |
| SIGN | This function determines the sign of a number. |
| SIN | This function gives the sine of a number. |
| SQRT | This function provides the square root of a number. |
| SQUARE | This function gives the square of a number. |
| SUM | This function finds the sum of a set of values. |
| TAN | This function provides the tangent of a number. |
SQL Server Date Functions
| SQL Server Function | Description |
|---|---|
| CURRENT_TIMESTAMP | This function provides the current date and time. |
| DATEADD | This function adds a specified time or date interval to a given date and returns the resulting date. |
| DATEDIFF | This function calculates and returns the difference between two dates. |
| DATEFROMPARTS | This function constructs a date using specified parts like year, month, and day values. |
| DATENAME | This function retrieves a specified part of a date as a string. |
| DATEPART | This function retrieves a specified part of a date as an integer. |
| DAY | This function gives the day of the month for a given date. |
| GETDATE | This function fetches the current date and time of the system. |
| GETUTCDATE | This function obtains the UTC date and time of the current system. |
| ISDATE | This function validates if an expression is a valid date (returns 1 for true, 0 for false). |
| MONTH | This function retrieves the month part for a specified date. |
| SYSDATETIME | This function retrieves the current date and time of the SQL Server. |
| YEAR | This function retrieves the year part for a specified date. |
SQL Server Aggregate Functions
SQL Server Aggregate Functions
| SQL Server Function | Description |
|---|---|
| AVG() | This function calculates and returns the average value from a numeric column. |
| COUNT() | This function tallies the number of rows retrieved in a SELECT statement. |
| FIRST() | This function retrieves the first value from the chosen column. |
| LAST() | This function fetches the last value from the chosen column. |
| MAX() | This function yields the maximum value found in the selected column. |
| MIN() | This function provides the minimum value present in the selected column. |
| SUM() | This function computes and returns the total sum of all values in the selected column. |
SQL Server Advanced Functions
| SQL Server Function | Description |
|---|---|
| CAST | This function changes the value of any data type to a specific data type. |
| COALESCE | This function delivers the first non-null value from a given list. |
| CONVERT | This function transforms a value of any data type into a specified data type. |
| CURRENT_USER | This function provides the name of the current user in the SQL Server database. |
| IIF | This function offers one value if a condition is TRUE, and another if it's FALSE. |
| ISNULL | This function provides a designated value if the expression is NULL, otherwise, it returns the expression. |
| ISNUMERIC | This function checks whether an expression is numeric. |
| NULLIF | This function results in NULL if two expressions are equal. |
| SESSION_USER | This function supplies the name of the current user in the SQL Server database. |
| SESSIONPROPERTY | This function gives the session settings for a specified option. |
| SYSTEM_USER | This function offers the login name for the current user. |
| USER_NAME | This function retrieves the database user name based on the specified ID. |
SQL Server Scalar Functions
| SQL Server Function | Description |
|---|---|
| UCASE() | This function updates the value of a field to uppercase. |
| LCASE() | This function updates the value of a field to lowercase. |
| MID() | This function fetches text from a text field. |
| LEN() | This function gives the length of the text string. |
| ROUND() | This function rounds a numeric field to the specified number of decimals. |
| NOW() | This function provides the current system date and time. |
| FORMAT() | This function defines how a field should be displayed. |
Conclusion
- SQL Server provides many functions for various data manipulation needs.
- The primary role of SQL functions is to simplify complex queries and automate repetitive tasks, enhancing the efficiency of database manipulation operations.
- The string functions help in manipulating textual data efficiently and the numeric functions facilitate mathematical operations and statistical analysis.
- SQL Server's date functions enable smooth handling of temporal data.
- SQL advanced functions offer more sophisticated data manipulation abilities.