[sql] T-SQL Substring - Last 3 Characters

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

if you want to specifically find strings which ends with desired characters then this would help you...

select * from tablename where col_name like '%190'

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

You can use either way:

SELECT RIGHT(RTRIM(columnName), 3)

OR

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

Because more ways to think about it are always good:

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