SQL Server RIGHT Function


By:

The RIGHT function is used to provide the right part of a string according to a specified number of characters.

Syntax

RIGHT(expression, NumberCharactersReturned)

Parameters

  • expression - this is the value, number or expression that we want use to get the right part.
  • NumberCharactersReturned - is the number of characters returned from the right.

Simple RIGHT Example

Below is a simple example of the RIGHT function returning the first 5 characters from the right.

SELECT RIGHT('Hello world', 5) as OUTPUT
right function results

Example of RIGHT using table columns

The next example shows the last 2 characters of the column FirstName from the Person table.

SELECT FirstName, RIGHT([FirstName],2) as OUTPUT
FROM [Person].[Person]
right function results

Example of RIGHT Function with CHARINDEX

A common use of the RIGHT function is when we need to get part of the string starting with some character.  We can use the CHARINDEX function to find the starting position and use this value with the RIGHT function.

In this example, we will get the position of the colon from the string and get the values to the right of the colon.

DECLARE @string nvarchar(20) = 'Example:New'

DECLARE @position int = CHARINDEX(':', @string)

DECLARE @length int = LEN(@string) - @position

SELECT RIGHT(@string, @length) as output
right function results

LEFT Function Error Message

If the NumberCharactersReturned is null, you will receive the following error message:

Invalid length parameter passed to the right function. 

The following example reproduces the error message:

SELECT RIGHT('Hello world', -5) as OUTPUT

To solve the problem, make sure that the NumberCharactersReturned parameter is positive:

SELECT RIGHT('Hello world', 5) as OUTPUT

Related Articles






Comments For This Article

















get free sql tips
agree to terms