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
This question is related to
sql
sql-server
tsql
Leaving database modeling issues aside. 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 digit-only studentids, try something like
SELECT * FROM STUDENTS WHERE STUDENTID LIKE '%[^0-9]%'
Just try below script:
Below code works only if studentid column datatype is varchar
SELECT * FROM STUDENTS WHERE STUDENTID like '%Searchstring%'
Try LIKE
construction, e.g. (assuming StudentId
is of type Char
, VarChar
etc.)
select *
from Students
where StudentId like '%' || TEXT || '%' -- <- TEXT - text to contain
Try this:
SElECT * FROM STUDENTS WHERE LEN(CAST(STUDENTID AS VARCHAR)) > 0
With this you get the rows where STUDENTID contains text
Suppose STUDENTID contains some characters or numbers that you already know i.e. 'searchstring' then below query will work for you.
You could try this:
select * from STUDENTS where CHARINDEX('searchstring',STUDENTID)>0
I think this one is the fastest and easiest one.
Source: Stackoverflow.com