PriceCheaperton PriceCheaperton - 2 months ago 7
SQL Question

Check if a column contains text using SQL

I have a column which is called

studentID
, but I have millions of records and somehow the application has input some arbitrary text in the column.

How do I search:

SELECT *
FROM STUDENTS
WHERE STUDENTID CONTAINS TEXT

Answer

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 -1.0e5

If you want to filter rows with student ids that contain only digits, try something like

SELECT * FROM STUDENTS WHERE STUDENTID LIKE '%[^0-9]%'