SQL Functions

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

 

Did you find this page useful?
+3  |  -0
(3 Votes)

Mini Learning Map


KEBI Academy Membership

Unlock your potential and succeed

Silver Easy start

$8per month

  • All BI Courses
  • All BI Video Lessons
  • All BI Text Lessons
  • Live Chat
  • .

Gold Learn Smarter

$16per month

  • Silver +
  • Member Forum
  • Progress Tracking
  • 70-461 Practice Tests
  • .

Platinum Progress fast

$24per month

  • Gold +
  • Webinar Recordings
  • DIY Projects
  • Boot Camps
  • .

Diamond Unstoppable

$40per month

  • Skype Video Coaching
  • Remote Database (coming soon)
  • Practice SQL Online (coming soon)
  • .
  • .
On Purchase you get
7 Days Free Trial
Subscriptions only
View more
60 days
Money back guarantee
View more
24/7
Access
View more
Instant Activation
via Email
View more
Easy & Anytime
Cancellation
View more
Price
Freeze
View more
Friendly
& helpful support
View more
New content
for free
View more
How to progress your career?
Frequently Asked Questions
Subscription gives you 7 days free trial and gives you re-occuring billing (auto-renewal) which means that your card (or payment account) will be charges automatically based on your selected frequency (monthly, quarterly or yearly). This option is ideal for those who want to use our membership long term without having to worry to extend it with option to cancel it at anytime.
Access pass does not give free trial and it is a one-off payment that gives access for fixed amount of time and which auto-expiries, this is ideal for those who want time limited access without having to worry about cancelling the membership.
7 days trial applies to subscription only and you will be asked to provide payment details (show commitment) but no payment will be taken during your 7 days trial. After 7 days, payment will be taken unless you decide to cancel your subscription. Cancelling is easy and even if you forget to cancel and payment is taken you still get 60 days money back guarantee so simply ask for refund.
You can cancel your subscription (including free trial) at any point in time.
There are many ways to cancel:
1. You can use your email payment receipt and click link with order number that ends with S.
2. You can send us an email (main menu).
3. You can call us (main menu).
4. You can use live chat (main menu).
It will depend on your goal. Please review membership benefits and explore our website including preview articles and videos.
If you want us to help you find out if our membership is suitable for the goal you want to achieve then please get in touch (main menu)
Our membership options were designed to fit most goals and budgets. If you are unsure which one is best for you then please contact us (main menu).
Yes, You can downgrade and upgrade your membership at any time. For more information contact us (main menu).
Yes, for more information contact us (main menu).
If you are on quarterly or yearly subscription you can cancel it and get money back for unused months, however we will not take into consideration the loyalty discount we give (33% off for Quarterly subscription and 50% off for Yearly Subscriptions) which means that if used months without discounts exceeds what you paid then there is no money back, otherwise you will get the difference back.
Fastspring is our retailer and we only sell membership to them. It's like going to a shop and buying a product in the shop instead of directly from the company who produces the product.
In our case, this actually makes the membership more affordable, as we have lower operational costs.
That occasionally happens. Make sure your card or paypal account has sufficient funds to cover the payment, it's not suspended, the card has not expiried and address matches your card address. Sometimes you may have to call your card provider to tell them you are happy to make this purchase. It's usually easier to try another card or account but if you want to use a particular card then contact us (main menu) and we will try to help you out.
Contact us (main menu) and we will help you out.
This is very rare and typical reasons are:
1. Mispelled email address.
2. Email went to spam folder.
3. Email was blocked.

We check activations reguarly and if we cannot reach you we will issue a refund usually after 30 days.
We try to make it very clear but this happens occasionaly so don't worry and simply contact us (main menu) and we will issue a refund and cancel your subscription.
There are many options like email, telephone, live chat and even skype. Use main menu to contact us.
We are based in UK and generally provide support between 8am and 8pm local time (GMT +0), Monday to Friday (business days) and occasionally during the weekeneds.
We will happily asnwer it. Please contact us (main menu).