T-SQL Substring - Last 3 Characters
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>
- Reverse the text
- Look for the first occurrence of a specif char (i.e. first occurrence FROM END of text). Gets the index of this char
- 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
- Reversed the reversed string to give you your desired substring