In this SQL Server tutorial, you will learn how to use the RIGHT function in SQL Server to extract the rightmost character from the given string value.
You will understand the process of extracting the characters from the right side of the literal string. Then from the string value within the table column and from the numeric values.
RIGHT Function in SQL Server
If you are analyzing data, then sometimes you need to extract the part of the string or data from a specific side (RIGHT or LEFT). So here you will understand how to extract the rightmost characters from the given string value.
The RIGHT function in SQL Server extracts the characters from the right side of the given string or value based on the specified number of characters.
The syntax is given below.
RIGHT(str_expression, length)Where,
- RIGHT(): It is the function itself for extracting the characters from the right side of the string value.
- str_expression: It is the string, value, or column from which you want to extract the specific number of characters. This str_expression can be any datatype except ntext and text datatype.
- length: The number of characters you want to extract from the right side of the given string such as 4 characters,6 characters,10 characters, etc. The length should positive number, in case of a negative length, the RIGHT () function returns an error.
Remember whatever the value (string expression) you provide to the RIGHT() function, it implicitly converts that value into nvarchar or varchar datatype. Also returns the extracted characters in the form of varchar or nvarchar datatype.
Let’s see with different examples how to extract the characters from the right side of the string.
RIGHT Function in SQL Server with Literal String
If you have a character string (literal string), you can extract the rightmost characters from that string.
For example, extract the 3 characters from the right side of the literal string ‘I live in the USA’. For that use the below query.
SELECT RIGHT('I live in the USA', 3);
As you can see in the above output, the RIGHT() function extracted the rightmost 3 characters that are ‘USA’ from the literal string ‘I live in the USA’.
So this is how you can extract the characters from the right side of the literal string using the RIGHT() function in SQL Server.
Next, let’s see how to use the RIGHT() function with the table column.
RIGHT Function in SQL Server with Table Column
You can use the RIGHT() function on the table column to extract the characters from the right side of the string value within that column.
For example, consider you have a Product table with columns ProductID, ProductName, and ProductCode which is shown below.
Look at the column ProductCode which contains product codes that are eight characters long. So you have a task of extracting the last three characters that represent the product category.
For that use the below query.
SELECT ProductCode, RIGHT(ProductCode, 3) AS RightExtractedProductCode
FROM Product;
The above query extracted the 3 characters from the ProductCode column that you can see in the output. For example, the 001 character is from the LT1001 product code, 303 from the REF303, and so on for all other product codes.
The query part RIGHT(ProductCode, 3) means extracting the 3 characters from the right side of each product code within the ProductCode column. This is how to use the RIGHT function in SQL Server with a table column.
RIGHT Function in SQL Server with Numeric Value
You can also extract the characters (digits) from the numeric value. But as you know the RIGHT() function works on the string value. So when you provide the numeric value, it implicitly converts that numeric value into a string (varchar or nvarchar) and then extracts the characters.
For example, you have numerical values such as 348398484 and want to extract 4 digits (characters) from the right side of this value. For that use the below query.
SELECT RIGHT(348398484,4) AS RightExtracted;
When you execute the above query, it extracts the 4 characters (digit) 8484 from the numeric value 348398484 that you can see in the above output.
But first, the RIGHT() function converts the numeric value into a string (varchar or nvarchar datatype), and then extracts the 4 characters from the right side of that string.
This is how you can use the RIGHT function in SQL Server with numerical values.
Remember RIGHT() function works with only a string value, if you pass any other kind of datatype except text and ntext, it first converts that datatype into a string (varchar or nvarchar) and then performs the extraction of the characters from the right side.
Conclusion
In this SQL Server tutorial, you learned how to extract the rightmost characters from the given string literal, from the table column, and then from the numeric value using the RIGHT() function.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.