Check if column contains text using SQL

Question

I have a column called studentID , but I have millions of records, and somehow the application enters some arbitrary text in the column.

How to search:

 SELECT * FROM STUDENTS WHERE STUDENTID CONTAINS TEXT 

Answer 1

The remaining problems are modeling the database. I think you can try

 SELECT * FROM STUDENTS WHERE ISNUMERIC(STUDENTID) = 0 

But ISNUMERIC returns 1 for any value that seems numeric, including things like -1.0e5

If you want to exclude students for numbers only, try something like

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

Answer 2

Try the LIKE construct, for example. (assuming StudentId is Char, VarChar, etc.)

  select * from Students where StudentId like '%' || TEXT || '%' -- <- TEXT - text to contain 

Answer 3

Just try below script:

The code below only works if the student data type is varchar

 SELECT * FROM STUDENTS WHERE STUDENTID like '%Searchstring%' 

Answer 4

Try the following:

 SElECT * FROM STUDENTS WHERE LEN(CAST(STUDENTID AS VARCHAR)) > 0 

With this you get the lines in which the STUDENTID contains the text

Answer 5

Suppose STUDENTID contains some characters or numbers that you already know, that is, “searchstring”, then the query below will work for you.

You can try this:

 select * from STUDENTS where CHARINDEX('searchstring',STUDENTID)>0 

I think this is the fastest and easiest.