how to match last character of string in TSQL?

Question

I would like to know if the last character of a string is an asterisk *

I have got the following which gives me the last character:

select RIGHT('hello*',1)

but how would I use it as a condition in an if statement by matching it? the following doesn't work as it only returns the last char.

select '*' =  RIGHT('hello*',1)

can I use regex?

Answer 1

You're just about there. By using:

select '*' =  RIGHT('hello*',1)

Sql evaluates the RIGHT but thinks you want to alias it as a column named *

You can use the expression conditionally:

if RIGHT('hello*',1) = '*'
    print 'Ends in *'
else
    print 'Does not end in *'

You can filter like so on a table:

select * 
from MyTable
where RIGHT(MyColumn, 1) = '*';

Although the performance won't be stellar. Edit : See Karl Kieninger's answer for ideas on how to greatly improve the performance of this query