fbpx

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…