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

回答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.

回答2:

You can use either way:

SELECT RIGHT(RTRIM(columnName), 3)

OR

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

回答3:

Because more ways to think about it are always good:

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

回答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

回答5:

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'