# SQL Server Scalar Functions (Non-aggregate)

SQL Server has many built-in functions.

This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.

### SQL Server String Functions

| Function      | Description                                                                       | Syntax                                                   |
| ------------- | --------------------------------------------------------------------------------- | -------------------------------------------------------- |
| CHAR          | Returns the character based on the ASCII code                                     | SELECT CHAR(65) AS CodeToCharacter;                      |
| CHARINDEX     | Returns the position of a substring in a string                                   | SELECT CHARINDEX('t', 'Customer') AS MatchPosition;      |
| CONCAT        | Adds two or more strings together                                                 | SELECT CONCAT('Gyansetu', '.com');                       |
| Concat with + | Adds two or more strings together                                                 | SELECT 'Gyansetu' + '.com';                              |
| CONCAT\_WS    | Adds two or more strings together with a separator                                | SELECT CONCAT\_WS('.', 'www', 'Gyansetu', 'com');        |
| LEFT          | Extracts a few characters from a string (starting from left)                      | SELECT LEFT('SQL Tutorial', 3) AS ExtractString;         |
| LEN           | Returns the length of a string                                                    | SELECT LEN('Gyansetu.com');                              |
| LOWER         | Converts a string to lower-case                                                   | SELECT LOWER('SQL Tutorial is FUN!');                    |
| LTRIM         | Removes leading spaces from a string                                              | SELECT LTRIM('     SQL Tutorial') AS LeftTrimmedString;  |
| REPLACE       | Replaces all occurrences of a substring within a string, with a new string        | SELECT REPLACE('SQL Tutorial', 'T', 'M');                |
| RIGHT         | Extracts a few characters from a string (starting from right)                     | SELECT RIGHT('SQL Tutorial', 3) AS ExtractString;        |
| RTRIM         | Removes trailing spaces from a string                                             | SELECT RTRIM('SQL Tutorial     ') AS RightTrimmedString; |
| STR           | Returns a number as string                                                        | SELECT STR(185);                                         |
| SUBSTRING     | Extracts some characters from a string                                            | SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString; |
| TRIM          | Removes leading and trailing spaces (or other specified characters) from a string | SELECT TRIM('     SQL Tutorial!     ') AS TrimmedString; |
| UPPER         | Converts a string to upper-case                                                   | SELECT UPPER('SQL Tutorial is FUN!');                    |

### SQL Server Math/Numeric Functions

| **Function** | **Description**                                                     | **Syntax**                                                  |
| ------------ | ------------------------------------------------------------------- | ----------------------------------------------------------- |
| ABS          | Returns the absolute value of a number                              | SELECT Abs(-243.5) AS AbsNum;                               |
| AVG          | Returns the average value of an expression                          | SELECT AVG(Price) AS AveragePrice FROM Products;            |
| CEILING      | Returns the smallest integer value that is >= a number              | SELECT CEILING(25.75) AS CeilValue;                         |
| COUNT        | Returns the number of records returned by a select query            | SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;  |
| FLOOR        | Returns the largest integer value that is <= to a number            | SELECT FLOOR(25.75) AS FloorValue;                          |
| MAX          | Returns the maximum value in a set of values                        | SELECT MAX(Price) AS LargestPrice FROM Products;            |
| MIN          | Returns the minimum value in a set of values                        | SELECT MIN(Price) AS SmallestPrice FROM Products;           |
| POWER        | Returns the value of a number raised to the power of another number | SELECT POWER(4, 2);                                         |
| ROUND        | Rounds a number to a specified number of decimal places             | SELECT ROUND(235.415, 2) AS RoundValue;                     |
| SQRT         | Returns the square root of a number                                 | SELECT SQRT(64);                                            |
| SQUARE       | Returns the square of a number                                      | SELECT SQUARE(64);                                          |
| SUM          | Calculates the sum of a set of values                               | SELECT SUM(quantity) as TotalItemsOrdered FROM OrderDetails |

### SQL Server Date Functions

| **Function**       | **Description**                                                  | **Syntax**                                                     |
| ------------------ | ---------------------------------------------------------------- | -------------------------------------------------------------- |
| CURRENT\_TIMESTAMP | Returns the current date and time                                | SELECT CURRENT\_TIMESTAMP;                                     |
| DATEADD            | Adds a time/date interval to a date and then returns the date    | SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;              |
| DATEDIFF           | Returns the difference between two dates                         | SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff; |
| DATEPART           | Returns a specified part of a date (as integer)                  | SELECT DATEPART(year, '2017/08/25') AS DatePartInt;            |
| DAY                | Returns the day of the month for a specified date                | SELECT DAY('2017/08/25') AS DayOfMonth;                        |
| GETDATE            | Returns the current database system date and time                | SELECT GETDATE();                                              |
| ISDATE             | Checks an expression and returns 1 if it is a valid date, else 0 | SELECT ISDATE('2017-08-25');                                   |
| MONTH              | Returns the month part for a specified date (1 - 12)             | SELECT MONTH('2017/08/25') AS Month;                           |
| SYSDATETIME        | Returns the date and time of the SQL Server                      | SELECT SYSDATETIME() AS SysDateTime;                           |
| YEAR               | Returns the year part for a specified date                       | SELECT YEAR('2017/08/25') AS Year;                             |

### SQL Server Advanced Functions

| **Function** | **Description**                                                                     | **Syntax**                                                                                                                           |
| ------------ | ----------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| CAST         | Converts a value (of any type) into a specified datatype                            | <p>CAST(expression AS datatype(length))<br> SELECT CAST(25.65 AS varchar);<br> SELECT CAST('2017-08-25' AS datetime);</p>            |
| CONVERT      | Converts a value (of any type) into a specified datatype                            | <p>CONVERT(data_type(length), expression, style)<br> SELECT CONVERT(int, 25.65);<br> SELECT CONVERT(varchar, '2017-08-25', 101);</p> |
| ISNULL       | Return a specified value if the expression is NULL, otherwise return the expression | SELECT ISNULL(NULL, 'Gyansetu.com');                                                                                                 |

{% file src="https://826093633-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LS8lPNzCGPR5-DLbGXv%2F-LSeeZYDcFlW7795jLbI%2F-LSegQ9bLNBeoFbmluxz%2FUseful%20SQL%20Server%20Functions.docx?alt=media&token=cf7a9994-7617-458d-a735-64ae73694b90" %}

