KEBI Academy [Log in]:
[Video: Watch or Download]

Learning Status:
Next Repetition Date: (testing)
Self Learning Report [Log in]

SQL Functions


+0  |  -0

Mini Learning Map

In this article we will provide you with SQL Functions list and step by step examples and provide blog post with common tasks that use the functions we have on the list. For the purpose of this article we will use SQL Server 2012.

Other Versions of SQL Server:

  • SQL Server 2012
    • This blog post has been written using SQL Server 2012
  • SQL Server 2008 / R2
    • Most of the function we use should work in SQL Server 2008 and R2
  • SQL Server 2005
    • Most common functions should work in SQL Server 2005

Who is it for?

  • People who would like to learn basics of using SQL Functions and also find out what functions are available with examples
  • Those who are preparing for Microsoft certifications and need to cover this topic.

 

SQL Function List:

  • String Functions
  • Aggregate Functions

 

String Functions

Aggregate Functions

 

 

RIGHT Function

Function Name:

RIGHT Function - returns the specified number of characters from a given input value starting from the end (right side).

Syntax

RIGHT(<<Input Value>>, <<Number of Characters>>)

Arguments:

<<Input Value>> - Provide input value (string, field name or expression) that will be used as your input.

<<Number of Characters>> Provide number of characters that you would like to receive from the <<Input Value>> you provided.

Simple Example:

SELECT RIGHT('ABC-123-BCD', 3)

Result = 'BCD'

Full Article:

SQL Right Function - Article that describes the RIGHT function in details and provides examples.

Right Function Microsoft Documentation

 

LEFT Function

Function Name:

LEFT Function - returns the specified number of characters from a given input value starting from the very beginning (left side)

Syntax

LEFT(<<Input Value>>, <<Number of Characters>>)

Arguments:

<<Input Value>> - Provide input value (string, field name or expression) that will be used as your input.

<<Number of Characters>> Provide number of characters that you would like to receive from the <<Input Value>> you provided.

Simple Example:

SELECT LEFT('ABC-123-BCD', 3)

Result = 'ABC'

Full Article:

SQL LEFT Function - Article that describes the LEFT function in details and provides examples.

LEFT Function Microsoft Documentation

 

SUBSTRING Function

Function Name:

SUBSTRING Function - returns portion of the specified  input value based on starting position and number of characters to be returned.

Syntax

SUBSTRING(<<Input Value>>, <<Starting Position>>, <<Number of Characters>>)

Arguments:

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.

<<Starting Position>> - Provide starting position which indicates where the portion of input value will be extract from.

<<Number of Characters>> - Provide number of characters that should be extracts (from Starting Position).

Simple Example:

SELECT  SUBSTRING ('ABC-123-BCD',3,5)
 
Result = 'C-123'
 
Full Article:

SQL SUBSTRING Function - Article that describe the SUBSTRING function in details..

SUBSTRING Function Microsoft Documentation

 

LOWER Function

Function Name:

 LOWER Function - returns a value after converting specified input value characters to lower case.

Syntax

 LOWER (<<Input Value>>)

Arguments:

<<Input Value>> - Provide input value (string, field name or expression) that will be used as your input.

Simple Example:

SELECT LOWER ('Abcd')
 
Result = 'abcd'
 
Full Article:

SQL LOWER Function - Article that describes the LOWER function in details..

LOWER Function Microsoft Documentation

 

UPPER Function

Function Name:

 UPPER Function - returns a value after converting specified input value characters to upper case.

Syntax

 UPPER (<<Input Value>>)

Arguments:

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.

Simple Example:

SELECT UPPER ('abcd')
 
Result = 'ABCD'
 
Full Article:

SQL UPPER Function - Article that describe the UPPER function in details..

UPPER Function Microsoft Documentation

 

RTRIM Function

 

Function Name:

 RTRIM Function - returns a value after removing any spaces from the right side of the specified input value.

Syntax

 RTRIM (<<Input Value>>)

Arguments:

<<Input Value>> - Provide input value (string, field name or expression) that will be used as your input.

Simple Example:
          

            SELECT RTRIM ('  abc  ')

 
Result =  '  abc'
 
Full Article:

SQL RTRIM Function - Article that describe the RTRIM function in details..

RTRIM Function Microsoft Documentation

 

LTRIM Function

Function Name:

 LTRIM Function - returns a value after removed any spaces from the left side of the specified input value.

Syntax

 LTRIM (<<Input Value>>)

Arguments:

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.

Simple Example:

SELECT LTRIM ('  abc  ')
 
Result =  'abc  '
 
Full Article:

SQL LTRIM Function - Article that describe the LTRIM function in details..

LTRIM Function Microsoft Documentation

 

LEN Function

Function Name:

 LEN Function - returns the number of characters of the specified input value.

Syntax

 LEN (<<Input Value>>)

Arguments:

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.

Simple Example:

SELECT LEN ('abc')
 
Result =  3
 
Full Article:

SQL LEN Function - Article that describe the LEN function in details..

LEN Function Microsoft Documentation

 

CHARINDEX Function

Function Name:

 CHARINDEX Function - Is used to find position of a character(s) in a string (field).

Syntax

 CHARINDEX (<<Value to Find>>, <<Input Value >>)

or

CHARINDEX (<<Value to Find>>, <<Input Value >>,<<Search Starting Position>>)

Arguments:

<<Value to Find>> - Provide value that you want to find in <<Input Value>>. 

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.

Optional:

<<Search Starting Position>> - Search of <<value to find>> starts from the beginning of <<input value>> however you can provide third argument that will specify starting position of the search.

Simple Example:

          SELECT CHARINDEX ('@', 'katie@katieandemil.co.uk' )

           Result = 6
 
SELECT CHARINDEX ('@', 'katie@katieandemil.co.uk',6 )

Result = 1
 
 
Full Article:

SQL CHARINDEX Function - Article that describe the CHARINDEX function in details..

CHARINDEX Function Microsoft Documentation

 

REVERSE Function

Function Name:

 REVERSE Function - Is used to return the reverse order of a string value.

Syntax:

 REVERSE (<<Input Value>>)

Arguments:

<<Input Value>> - Provide string value (field name or expression) that will be used as your input.

Simple Example:

SELECT REVERSE ('abc')

Result = 'cba'
 
Full Article:

SQL REVERSE Function - Article that describe the REVERSE function in details.

REVERSE Function Microsoft Documentation

 

Replace Function

 

Function Name:

 REPLACE Function - Is used to find value and replace it with specified replacement value.

Syntax

REPLACE ( <<Input Value>> ,<< ValueToFind>>, <<ReplacementValue>> )

Arguments:

<<Input Value>> - Provide string value (field name or expression) that will be used as your input.

<<ValueToFind>> -Provide substring to be found.

<<ReplacementValue>> -Provide string to replace.

Simple Example:

SELECT Replace('Katie & Emil', '&', 'and')

Result = 'Katie And Emil'
 
Full Article:

SQL Replace Function - Article that describe the REVERSE function in details.

Replace Function Microsoft Documentation

 

COUNT Function

Function Name:

 COUNT Function - Is used to count rows in a table.

Syntax

COUNT ( <<Input Value>> )

 

Arguments:

<<Input Value>> - Provide input value (field name or expression) that will be used as your input.
 

Simple Example
 

            SELECT COUNT(*)

            FROM [dbo].[DimCustomer]
 
 Result = 18484
 
 
 
Full Article:

SQL COUNT Function - Article that describe the COUNT function in details.

COUNT Function Microsoft Documentation

 

 

SUM Function

Function Name:

 SUM Function - returns the total sum of a numeric column.

Syntax

SUM ( <<Input Value>> )

Arguments:

<<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.

Simple Example
 

           SELECT Sum(SalesAmount) As TotalSalesAmount

           FROM [dbo].[FactInternetSales]
 
 Result =  29358677.2207
 
 
Full Article:

SQL SUM Function - Article that describe the SUM function in details.

SUM Function Microsoft Documentation

 

AVG Function

Function Name:

 AVG Function - returns the average value of a numeric column.

Syntax

AVG ( <<Input Value>> )

Arguments:

<<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.

Simple Example


             
             SELECT Avg(SalesAmount) As AverageSalesAmount
 
             FROM [dbo].[FactInternetSales]

   

Full Article:

SQL AVG Function - Article that describe the AVG function in details.

AVG Function Microsoft Documentation

 

 

MIN Function

Function Name:

MIN Function - returns the Minimum value of a numeric column.

Syntax

MIN ( <<Input Value>> )

Arguments:

<<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.

Simple Example

             SELECT MIN(SalesAmount) As MinSalesAmount
             
             FROM [dbo].[FactInternetSales]
             
              Result = 2.29

   

Full Article:

SQL MIN Function - Article that describe the MIN function in details.

MIN Function Microsoft Documentation

 

 

MAX Function

Function Name:

MAX Function - returns the Maximum value of a numeric column.

Syntax

MAX ( <<Input Value>> )

Arguments:

<<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.

Simple Example

             SELECT MAX(SalesAmount) As MinSalesAmount
 
             FROM [dbo].[FactInternetSales]
             
             Result = 3578.27
     

   

Full Article:

SQL MAX Function - Article that describe the MAX function in details.

MAX Function Microsoft Documentation

 


KEBI Academy is open!

You can now join our KEBI Academy and just learn more.

How much? It's voluntary payment so you decide how much you want to contribute to what you get out of it.

Join KEBI Academy

Comments



Add Comment
Name: pouletfritesmayo
Date Posted: 4/3/2013 10:18:25 AM
Comment:

Thank you for you great job.

I start learning  sql by these good tutorials.




Visitor Voting

We are thinking about re-introducing webinars (SQL, SSIS, SSRS, SSAS) but this time for members only (due to effort).
Is that something you might be interested in?
Visitor Voting: Yes +33  |  No -7