This SQL Tutorial provides you a summary of some of the most common Built-in Scalar Functions in SQL Server. For this lesson’s exercises, use this link.
SQL Server Scalar Functions allow you to perform different calculations on data values. These functions operate on single rows only and produce one result per row. There are different types of Scalar Functions, this tutorial covers the following:
- String functions – functions that perform operations on character values.
- Numeric functions – functions that perform operations on numeric values.
- Date functions – functions that perform operations on date values.
- Conversion functions – functions that convert column data types.
- NULL-related Functions – functions for handling null values.
Note: this tutorial focuses on SQL Server Scalar Functions; for more details about Group Functions, use the following link
SQL Server String Functions
|
Syntax |
Description |
Function |
SELECT RIGHT('hello' , 2)
-- Result: 'lo'
|
Returns the right part of a character string with the specified number of characters. | |
SELECT LEFT('hello' , 2)
-- Result: 'he'
|
Returns the left part of a character string with the specified number of characters. | |
SELECT LEN('hello')
-- Result: 5
|
Returns the number of characters of the specified string expression. | |
SELECT RTRIM(' hello ')
-- Result: ' hello'
|
Returns a character string after truncating all trailing blanks. | |
SELECT LTRIM(' hello ')
-- Result: 'hello '
|
Returns a character expression after it removes leading blanks. | |
SELECT REPLACE('hello' , 'e' , '$')
-- Result: 'h$llo'
|
Replaces all occurrences of a specified string value with another string value. | |
SELECT REVERSE('hello')
-- Result: 'olleh'
|
Returns the reverse order of a string value. | |
SELECT SUBSTRING('hello' , 2,3)
-- Result: 'ell'
|
Returns part of a text. | |
SELECT LOWER('HELLO')
-- Result: 'hello'
|
Returns a character expression after converting uppercase character data to lowercase. | |
SELECT UPPER('hello')
-- Result: 'HELLO'
|
Returns a character expression with lowercase character data converted to uppercase. |
SQL Server Date Functions
| Syntax |
Description |
Function |
SELECT DATEADD (mm, 3 ,'2010-01-01') -- Result: '2010-04-01' SELECT DATEADD (dd , 3 ,'2010-01-01') -- Result: '2010-01-04' SELECTDATEADD (yy , 3 ,'2010-01-01') -- Result: '2013-01-01' |
Returns a specified date with the specified number interval added to a specified datepart of that date. | |
SELECT DATEDIFF (yy,'2009-01-01','2010-01-01') -- Result: 1 SELECT DATEDIFF (mm,'2009-01-01','2010-01-01') -- Result: 12 SELECT DATEDIFF (dd,'2009-01-01','2010-01-01') -- Result: 365
|
Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate. | |
SELECT DAY('2010-01-01')
-- Result: 1
|
Returns an integer representing the day (day of the month) of the specified date. | |
SELECT MONTH('2010-01-01')
-- Result: 1
|
Returns an integer that represents the month of the specified date. | |
SELECT YEAR('2010-01-01')
-- Result: 2010
|
Returns an integer that represents the year of the specified date. | |
SELECT GETDATE() -- Result: (current date)
|
Returns the current database system date. This value is derived from the operating system of the computer on which the instance of SQL Server is running. |
SQL Server Numeric Functions
|
Syntax |
Description |
Function |
SELECT FLOOR(59.9) -- Result: 59 |
Returns the largest integer less than or equal to the specified numeric expression. | |
SELECT CEILING(59.1) -- Result: 60 |
Returns the smallest integer greater than, or equal to, the specified numeric expression. | |
SELECT ROUND(59.9, 0) -- Result: 60 SELECT ROUND(59.1, 0) -- Result: 59 |
Returns a numeric value, rounded to the specified length or precision. |
SQL Server Conversion Functions
|
Syntax |
Description |
Function |
SELECT CAST(getdate() AS varchar) -- Result: Jan 11 2013 4:22PM |
Converts an expression of one data type to another |
|
SELECT CONVERT(varchar,getdate(),103) -- Result: 11/01/2013
|
Converts an expression of one data type to another |
SQL Server NULL-Related Functions
|
Syntax |
Description |
Function |
SELECT ISNULL(NULL,'Somevalue') -- Result: Somevalue |
Replaces NULL with the specified replacement value. |
