WITH ListValues AS ( SELECT N'123' AS Value UNION SELECT N'ABC' UNION SELECT '12AB' )
SELECT Value + ' : ' +
       ,  CASE ISNUMERIC(Value)
            WHEN 1 THEN 'IS NUMERIC'
            WHEN 0 THEN 'IS NOT NUMERIC'
         END
FROM ListValues

123 : IS NUMERIC
12AB : IS NOT NUMERIC
ABC : IS NOT NUMERIC