Contact Us

SQL Server Scalar Functions

 

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:

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.

RIGHT

 

SELECT LEFT('hello' , 2)
-- Result: 'he'
Returns the left part of a character string with the specified number of characters.

LEFT  


SELECT LEN('hello')
-- Result: 5

 

 Returns the number of characters of the specified string expression.

LEN

 

SELECT RTRIM(' hello    ')
-- Result: ' hello'
Returns a character string after truncating all trailing blanks.

RTRIM

 

SELECT LTRIM('  hello    ')
-- Result: 'hello    '
Returns a character expression after it removes leading blanks.

LTRIM

 

SELECT REPLACE('hello' , 'e' , '$')
-- Result: 'h$llo'
Replaces all occurrences of a specified string value with another string value.

REPLACE

 

SELECT REVERSE('hello')
-- Result: 'olleh'
Returns the reverse order of a string value.

REVERSE

 

SELECT SUBSTRING('hello' , 2,3)
-- Result: 'ell'
Returns part of a text.

SUBSTRING

 

SELECT LOWER('HELLO')
-- Result: 'hello'

 

 Returns a character expression after converting uppercase character data to lowercase.

LOWER

 

SELECT UPPER('hello')
-- Result: 'HELLO'

 

 Returns a character expression with lowercase character data converted to uppercase.

UPPER

 

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.

DATEADD

 

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.

DATEDIFF

 

SELECT DAY('2010-01-01')
-- Result: 1

 

 Returns an integer representing the day (day of the month) of the specified date.

DAY

 

SELECT MONTH('2010-01-01')
-- Result: 1

 

 Returns an integer that represents the month of the specified date.

MONTH

 

SELECT YEAR('2010-01-01')
-- Result: 2010

 

 Returns an integer that represents the year of the specified date.

YEAR

 

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.

GETDATE()

 

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.

FLOOR

 

SELECT CEILING(59.1)
-- Result: 60
Returns the smallest integer greater than, or equal to, the specified numeric expression.

CEILING

 

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.

ROUND

 

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

CAST

 

SELECT CONVERT(varchar,getdate(),103)
-- Result: 11/01/2013

 

Converts an expression of one data type to another

CONVERT

 

SQL Server NULL-Related Functions

 

Syntax

Description

Function

 

SELECT ISNULL(NULL,'Somevalue')
-- Result: Somevalue
Replaces NULL with the specified replacement value.

ISNULL

 

 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…