Tutorial Gateway

SQL RIGHT Function

by

The SQL RIGHT function is a String Function used to return the rightmost characters from a given expression. SQL Server RIGHT function uses its second argument to decide the number of characters it should return. The syntax of the SQL Server RIGHT Function is

SELECT RIGHT (Character_Expression, Value)
FROM [Source]
  • Character_Expression: RIGHT Function will write the rightmost characters from this expression.
  • Value: Number of characters right function has to extract from the Character_Expression.

For this RIGHT Function in SQL Server example, We use the below-shown data

SQL RIGHT FUNCTION

TIP: The index position in the RIGHT Function will start from 1, Not 0.

SQL RIGHT Function Example 1

The RIGHT function returns the specified number of rightmost characters from the given string. The following RIGHT function query returns the rightmost 6 and 20 characters from a variable.

DECLARE @Charcater_Expression varchar(50)
SET @Charcater_Expression = 'Learn SQL Server' 

--Using Positive Integer 6
SELECT RIGHT (@Charcater_Expression, 6) AS 'SQL RIGHT' 

--Searching from specific position – 20
SELECT RIGHT (@Charcater_Expression, 20) AS 'SQL RIGHT'

OUTPUT

SQL RIGHT FUNCTION 1

ANALYSIS

We used the RIGHT function to return six rightmost characters from the @Character_Expression variable.

--Using Positive Integer 6
SELECT RIGHT (@Charcater_Expression, 6) AS 'SQL RIGHT'

In the next line, We set the second argument = 20, which is greater than the string length. So, String Right function will return all the characters from the @Character_Expression

--Searching from specific position – 20
SELECT RIGHT (@Charcater_Expression, 20) AS 'SQL RIGHT'

SQL RIGHT Function Example 2

The RIGHT function also allows you to select the rightmost characters from the column values. In this SQL Server example, we are going to return the rightmost nine words of all the records present inside the Email column

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,RIGHT ([Email], 9) AS [SQL RIGHT]
 FROM [Employe]

OUTPUT

SQL RIGHT FUNCTION 2

If you observe the above result, string Right Function is not getting the domain names perfectly because we are using fixed value. In the next String Function example, we show how to use dynamic value as a second argument to return the domain names accurately.

Find Domain Names in Email using SQL RIGHT Function

We will find the domain names present in the Email column using the right Function.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,RIGHT (
              [Email]
             ,(LEN([Email]) - CHARINDEX ('@', [Email]))
             ) AS [SQL RIGHT]
 FROM [Employe]

OUTPUT

SQL RIGHT FUNCTION 3

ANALYSIS:

Within the Sql Server right function, below statement will find the length of Email using LEN Function

LEN([Email])

The below statement returns the index position of the @ symbol. It means the CHARINDEX function check for the index position of @ symbol in every record.

CHARINDEX ('@', [Email])

We subtracted the index position from the Length of a string using the below statement.

(LEN([Email]) - CHARINDEX ('@', [Email]))

Placed Under: SQL