I have a column which is called
WHERE STUDENTID CONTAINS TEXT
Leaving database modeling issues aside. I think you can try
SELECT * FROM STUDENTS WHERE ISNUMERIC(STUDENTID) = 0
But this will accept any number that seems numeric including things like
If you want to filter rows with student ids that contain only digits, try something like
SELECT * FROM STUDENTS WHERE STUDENTID LIKE '%[^0-9]%'