T-SQL Substring - Last 3 Characters

Question

Using T-SQL, how would I go about getting the last 3 characters of a varchar column?

So the column text is IDS_ENUM_Change_262147_190 and I need 190

Solution 1

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.

Solution 2

You can use either way:

SELECT RIGHT(RTRIM(columnName), 3)

OR

SELECT SUBSTRING(columnName, LEN(columnName)-2, 3)

Solution 3

Because more ways to think about it are always good:

select reverse(substring(reverse(columnName), 1, 3))

Solution 4

declare @newdata varchar(30)
set @newdata='IDS_ENUM_Change_262147_190'
select REVERSE(substring(reverse(@newdata),0,charindex('_',reverse(@newdata))))

=== Explanation ===

I found it easier to read written like this:

SELECT
    REVERSE( --4.
        SUBSTRING( -- 3.
            REVERSE(<field_name>),
            0,
            CHARINDEX( -- 2.
                '<your char of choice>',
                REVERSE(<field_name>) -- 1.
            )
        )
    )
FROM
    <table_name>

1. Reverse the text
2. Look for the first occurrence of a specif char (i.e. first occurrence FROM END of text). Gets the index of this char
3. Looks at the reversed text again. searches from index 0 to index of your char. This gives the string you are looking for, but in reverse
4. Reversed the reversed string to give you your desired substring

View more solutions

Comments

[MatBailie]

If you are going to search large volumes of data this way, there is a way to recover use of indexes. Create a computed column that is the string in reverse order. Then what were the right (least significant) three characters is now the left (most significant) three characters. You then index that column and look for WHERE LEFT(reversed,3) = REVERSE('190'). (I'm bored, can you tell?)

[Adam Robinson]

No, more is not always better.

[Ben Thul]

You're right. You'll always have all the tools that you need to accomplish whatever job you're trying to do. Knowledge doesn't equal power, I guess.

[Adam Robinson]

I'm not really sure what that's supposed to mean. The solution that you propose is unnecessarily complex, and there's no circumstance in which it would be the right solution. Hence, it's bad.

[Ben Thul]

Welcome to the world of software development, where an "easy" isn't always possible. If your one of your criteria for whether a solution is right or not is whether it's complex, you're in the wrong business. Am I saying that I'd use this solution given the stated problem? No. What I am saying is that there's value in seeing multiple solutions because it broadens your horizons to what's out there. Perhaps the OP didn't know about the reverse function prior to this. Now s/he does. My opinion of downvotes on here is that they should be reserved for obviously incorrect solutions. Mine works

[Adam Robinson]

Extended homilies on the nature of software development and my apparent lack of qualification in the industry are not topics relevant in this forum, so I'm not going to address them here. Downvotes indicate that an answer is either wrong or "bad" on some level. Your answer is bad because it is needlessly complex; would an answer that added two or four more calls to reverse be good as an additional answer because it's different from your answer? What about if I encrypted and unencrypted the string? You're saying that arbitrary useless complexity is somehow not a consideration.

[FooF]

Stackoverflow.com comments can accept formatting by simple mark-up (mark-down format). There is help available. To show segment of code, you can prefix each line with four spaces.

[BradleyDotNET]

This doesn't answer the question. The OP wants to know how to get the last 3 characters, no matter what they are, not a specific string like 190.

[Brinky]

The OP stated he wanted the last 3 characters of a string. REVERSE is the most direct way of working immediately with the last part of a string in SQL.

[noelicus]

Is there an advantage one way over the other? And is the RTRIM necessary (seems superfluous to me)?

[Elias Hossain]

The job can be done in several ways and here I've shown using common two ways. First case is faster than second way. RTRIM is not mandatory. It is used just to omit any extra space at right side. Thanks