SQLTeam.com | Weblogs | Forums

Gert values from right 4 characters example 81241001 = 8124


How would i use the substring function to get values starting from the right 4 values'
example 874121001 should equal 87412
2151001 = 215

if i use substring (874121001 , 1,5) that would work however
if a vaule within the column is like substring(2151001 , 1,5) would return 01 which is not correct it should be 215

please advise


Use RIGHT rather than SUBSTRING:

RIGHT(column|value, 4)


thats not correct right(85421001,4) will bring back 1001

i dont want to return 1001 i want to only return 8542, the problem is some values may have lets sat 5671001, 87651001,101001. i want to get rid of the 1001 and return all preceeding values . so RIGHT will not work i already tried that

thx for your help


Oh, leave off the right-most four chars?

LEFT(column, LEN(column) - 4))


Any chance the (significant) right-most characters will be spaces?

LEN(column) will not include any trailing spaces. If that's a problem then use DATALENGTH(column) instead.