pls try below code

select * from table where right(ltrim(rtrim(name,3)))='red'

or left(ltrim(rtrim(name,3)))='red'