Overview
The RIGHT function is used to provide the right part of a string according to a specified number of characters.
Explanation
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
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]
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
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 OUTPUTTo solve the problem, make sure that the NumberCharactersReturned parameter is positive:
SELECT RIGHT('Hello world', 5) as OUTPUTAdditional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
