Tip of the Day : Example Uses of the REVERSE String Function

SQL Server Helper - Tip of the Day

Example Uses of the RIGHT String Function

The RIGHT string function returns the right part of a character string with the specified number of characters. The syntax of the RIGHT string function is as follows:

RIGHT( <character expression>, <integer expression> )

The first parameter of the RIGHT string function is a character expression of character or binary data type from which to extract characters.  The <character expression> parameter can be of any data type, except TEXT or NTEXT, that can be implicitly converted to VARCHAR or NVARCHAR.  The second parameter is a positive integer expression indicating the number of characters of the <character expression> to extract and return.

Here are a few examples on the uses of the RIGHT string function:

Usage #1 : Extract Last Name from a Full Name

DECLARE @FullName VARCHAR(100) = 'Donald Duck'
SELECT RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName)) - 1) AS [LastName]
LastName
----------
Duck

Usage #2 : Extract File Extension from a File Name

DECLARE @FileName VARCHAR(100) = 'sqlserver.exe'
SELECT RIGHT(@FileName, CHARINDEX('.', REVERSE(@FileName))) AS [FileExtension]
FileExtension
---------------
.exe

Usage #3 : Extract the Page Name from a URL

DECLARE @PageName VARCHAR(2000) = 'http://www.sql-server-helper.com/default.aspx'
SELECT RIGHT(@PageName, CHARINDEX('/', REVERSE(@PageName)) - 1) AS [PageName]
PageName
--------------
default.aspx

Usage #4 : Right-Justify a String

DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT RIGHT(SPACE(25) + @Header1, 25)
PRINT RIGHT(SPACE(25) + @Header2, 25)
PRINT RIGHT(SPACE(25) + @Header3, 25)
   SQL Server Helper, LLC
          1234 1st Street
 Somewhere City, NY 01001

Usage #5 : Pad a Numeric Value with Leading Zeros When Converted to a String

DECLARE @StudentNumber INT = 12498
SELECT RIGHT('0000000000' + CAST(@StudentNumber AS VARCHAR(10)), 10) AS [StudentNumber]
StudentNumber
----------------
0000012498
Back to Tip of the Day List Next Tip