# 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 | CAST(expression AS datatype(length))
SELECT CAST(25.65 AS varchar);
SELECT CAST('2017-08-25' AS datetime);
|
| CONVERT | Converts a value (of any type) into a specified datatype | CONVERT(data\_type(length), expression, style)
SELECT CONVERT(int, 25.65);
SELECT CONVERT(varchar, '2017-08-25', 101);
|
| ISNULL | Return a specified value if the expression is NULL, otherwise return the expression | SELECT ISNULL(NULL, 'Gyansetu.com'); |
{% file src="" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://gyansetu-sql.gitbook.io/sql-programming/sql-select/sql-server-functions.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.