Now, there's a small problem. What happens if you ever get a value like 'a23'?

You should use a numeric datatype, and for even/odd numbers problem, the best type could be int.

To find out if a number is even or odd, the classic way to go is with a modulo operator.

WITH Tally(n) AS(

SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1

FROM sys.all_columns)

SELECT n, CASE WHEN n % 2 = 1 THEN 'Odd' ELSE 'Even' END

FROM Tally