SQL server has many built in functions -SQL Server Functions . Now many of us might be thinking that what are SQL Server functions?, What are Built in functions?, How they work , and so on . This Article will guide you through Different SQL built in functions , and will discuss about the use of them .
What is Built in Functions?
Built in Functions are the piece of programming language that returns a output for the following input.
Ex: abs() abs function is used to return the absolute value.
SQL server functions -Numeric function [ABS()]-SELECT Abs(-1000.09) AS Example;
O/P-Function of ABS() is to return the absolute value., and absolute value is to be stored in Example .
| Example |
| 1000 |
ABS(), COUNT(),RAND(), MIN(),ASCII() , and so on are the examples of SQL Server functions. Let us see each of them in detail, and enjoy the topic.
SQL Server String Functions
The following functions mentioned below are the Built-in functions in SQL with a “String “ type.
| Function | Description |
|---|---|
| ASCII | Returns the ASCII value of the given Input (ASCII value-number ) |
| CHAR | Returns the ASCII character based on the given Input |
| CHARINDEX | Returns the location of a substring in a string |
| CONCAT | It’s function is to concatenate 2 strings. |
| Concat with + | Same function as CONCAT, but it can concatenate ‘2’ or more Strings |
| DATALENGTH | Returns the length( in bytes), Expression Length is calculated using this function |
| LEFT | Extracts a substring from a string (starting from left) |
| LEN | Returns the length of the specified string , EX:2,3,4,any numeric value |
| LOWER | A string is converted to a lowercase |
| LTRIM | It removes spaces from a string |
| NCHAR | Returns the Unicode character based on the Input mentioned |
| PATINDEX | Returns the particular location of a pattern mentioned in the string |
| REPLACE | Replaces a sequence of characters in a string with another set of characters |
| RIGHT | Function Extracts a sub-string from a string (*from Right) |
| RTRIM | Removes trailing spaces from a string |
| SPACE | Returns a string with a specified number of spaces |
| STR | It represents the string value of a particular number |
| STUFF | Function Deletes a sequence of characters from a string and then insert the new pattern at the specified position. |
| SUBSTRING | It extracts a Substring from a given string |
| UPPER | It converts a String to Uppercase |
SQL Server Numeric Functions
The following functions mentioned below are the Built-in Functions in SQL, and works on the numeric data.
| Function | Description |
|---|---|
| ABS | It Returns the absolute value of a number |
| AVG | It Returns the average value of an expression |
| CEILING | It Returns the smallest integer value that is greater than or equal to a num |
| COUNT | It Returns the count of an Input(Expression). |
| FLOOR | Function is same as of CEILING , Returns the largest integer value that is equal to or less than a number |
| MAX | It Returns the maximum value of an expression |
| MIN | It Returns the minimum value of an expression |
| RAND | It Returns a random number , The function produces a random number and it changes every second. |
| ROUND | It Returns a number rounded to a certain number of decimal places |
| SIGN | It Returns a value indicating the sign of a number |
| SUM | the summed value of an expression is returned |
SQL Server Conversion Functions
SQL Server Conversion Functions are different from ‘String” , and other built in functions. They are used to change the Data type of an expression.
| Function | Description |
|---|---|
| CAST | function helps to change the form of data type. |
| CONVERT | CONVERT functions has the same functionality as of CAST , change the data type of an expression. |
SQL Server Advanced Functions
This are some Advanced Built-in Functions used for the SQL. Advance function do not fall under the category of -‘String’ , ‘Numeric’,and other categoties of built-in functions.
| Function | Description |
|---|---|
| COALESCE | Function is to Return the non-null expression in a list |
| CURRENT_USER | As the function name suggest , It Returns the name of the current user in the SQL database. |
| ISDATE | Returns 1 for the valid date , and ‘0’ for not. Functionality is same as of “BOOLEAN” but returns ‘1’ and ‘0’ |
| ISNULL | As the name of the function suggests, It return an alt. value when an Expression=NULL value. |
| ISNUMERIC | Returns 1 if the expression is a valid number, otherwise 0 |
| NULLIF | NULLIF compares ‘2’ expressions and gives the output |
| SESSION_USER | Returns the user name of the current session in the SQL database |
| SESSIONPROPERTY | Returns the setting for a specified option of a session |
| SYSTEM_USER | Returns the login name information for the current user in the SQL database. |
| USER_NAME | Returns the user name in the SQL Server database |
I hope that you like this article , for any doubts please drop your questions in the comment section down below.