How to Use Left & Right Functions in SQL to Get Last 3 Characters

How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?

SELECT  RIGHT(RTRIM(column), 3),
        LEFT(column, LEN(column) - 3)
FROM    table

Use RIGHT w/ RTRIM (to avoid complications with a fixed-length column), and LEFT coupled with LEN (to only grab what you need, exempt of the last 3 characters).

if there's ever a situation where the length is <= 3, then you're probably going to have to use a CASE statement so the LEFT call doesn't get greedy.

T-SQL Substring - Last 3 Characters

SELECT RIGHT(column, 3)

That's all you need.

You can also do LEFT() in the same way.

Bear in mind if you are using this in a WHERE clause that the RIGHT() can't use any indexes.

SQL query to display the length and first 3 characters of ename column in emp table

Hi Shanu,
You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column.

SELECT LEN(column_name) FROM table_name;

And you can use SUBSTRING or SUBSTR() function go get first three characters of a column.

SUBSTRING( string, start_position, length );
SELECT SUBSTRING( column_name, 1, 3 ) FROM table_name;

To get both together use concatenation operator,

 SELECT LEN(column_name)||SUBSTRING( column_name, 1, 3 ) FROM table_name;

Hope you got what you need. Any issues, feel free to ask

RIGHT() / LEFT() functions

There is no right or left function, but you can implement the same functionality with substr, like this:

left(column, nchar) = substr(column, 1* nchar)

right(column, nchar) = substr(column, (-1)* nchar)

Here nchar is number of characters.

How to take last four characters from a varchar?

Right should do:

select RIGHT('abcdeffff',4)

Order by last 3 chars

This will do it, very simply selecting the right-most 3 characters and ordering by that value ascending.

SELECT *
FROM table_name
ORDER BY RIGHT(name, 3) ASC;

It should be added that as your data grows, this will become an inefficient solution. Eventually, you'll probably want to store the numeric appendix in a separate, indexed integer column, so that sorting will be optimally efficient.

How to select a substring but leaving out the last character if is an alphabet in SQL

Find a position of the rightmost digit and take left symbols of the string including this position

DECLARE @CUSTOMER_ID1 VARCHAR(50);
SELECT @CUSTOMER_ID1 = 'RS/123/2017SA';

select left(@CUSTOMER_ID1, len(@CUSTOMER_ID1) - patindex('%[0-9]%', reverse(trim(@CUSTOMER_ID1))) + 1) customer_ID; 

U-SQL get last 3 letters of a string

You need to use the C# equivalents, which don't actually include an analogue for a SQL right. Consequently you need to use Substring, which naively looks like this:

MyText.Substring(MyText.Length-3)

This starts the substring at the number of characters in the first argument and, if no second argument is provided, continues to the end of the string.

Being C# however, everything is a little less user friendly than SQL and will error out if your string is less than 3 characters long, as a string that is only 2 characters long will result in a start index of 2 - 3 = -1, which is not allowed. So a slightly more robust solution would be:

MyText.Length < 3
  ? MyText
  : MyText.Substring(MyText.Length-3)

That returns the entire string when it is shorter than 3 characters. This too will have problems though, if your strings can hold null values. So to be even more robust we can add a ?? check, which is the equivalent of a SQL isnull():

(MyText ?? String.Empty).Length < 3
  ? MyText
  : MyText.Substring(MyText.Length-3)

Find index of last occurrence of a sub-string using T-SQL

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

---

Related Topics