SUBBIS
SUBB1D
SUBBD3
SUBB12

where column like '%[0-9][0-9]'

SELECT MyId,
ISNUMERIC(RIGHT(MyColumn,2)) -- your column to check last 2 (if numeric)
FROM (
----- replace with your table
SELECT 1 MyId,'SUBBIS' MyColumn UNION SELECT 2,'SUBB1D' UNION
SELECT 3,'SUBBD3' UNION  SELECT 4,'SUBB12'
----- replace with your table
) A