Tutorial Gateway

SQL RIGHT Function

by

The SQL RIGHT function is one of the String Function, which is used to return right most characters from the specified expression. RIGHT function uses its second argument to decide, How many characters it should return.

SQL RIGHT Function Syntax

The basic syntax of the SQL RIGHT Function in SQL Server is as shown below:

SELECT RIGHT (Character_Expression, Value)
FROM [Source]
  • Character_Expression: Please specify the valid Expression on which to want to perform search operation. RIGHT Function will write the right most characters from this expression.
  • Value: Please specify, how many characters you want to extract from the Character_Expression.

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

In this article we will show you, How to write RIGHT Function in SQL Server with example. For this, We are going to use the below shown data

SQL RIGHT FUNCTION

SQL RIGHT Function Example 1

The RIGHT Function is used to return the specified number of rightmost characters from the given string. The following query will show multiple ways to use SQL server RIGHT function.

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

Below lines of code is used to declare string variable and assigning the string data.

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

In below statement, We used RIGHT function to return six rightmost characters from the @Character_Expression variable. We also assigned new name to that result as ‘SQL RIGHT using ALIAS Column.

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

In the next line, We set the second argument as 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 right most characters from the column values. In this 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 example we will show you, How to use dynamic value as a second argument to return the domain names accurately.

Find Domain Names in Email using SQL RIGHT Function

In this example, We are going to find the domain names present in Email column using Sql 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 right function, below statement will find the length of Email using LEN Function

LEN([Email])

Below statement will return the index position of the @ symbol. It means, CHARINDEX function will check for the index position of @ symbol in each and 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