CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Jared
Jared

Reputation: 2483

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

Upvotes: 80

Views: 191382

Answers (5)

user2176274
user2176274

Reputation: 21

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

Upvotes: 2

sharath
sharath

Reputation: 1

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'

Upvotes: -3

Ben Thul
Ben Thul

Reputation: 32707

Because more ways to think about it are always good:

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

Upvotes: 15

Elias Hossain
Elias Hossain

Reputation: 4469

You can use either way:

SELECT RIGHT(RTRIM(columnName), 3)

OR

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

Upvotes: 24

JNK
JNK

Reputation: 65157

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.

Upvotes: 136

Related Questions