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
-
1Check this link. I assume that if you use CONTAINS, you are using Full text catalog.Gaston Flores– Gaston Flores2013-06-26 14:08:49 +00:00Commented Jun 26, 2013 at 14:08
-
1Are you looking for "Text" or are you looking for any non-numeric text? It makes a difference to the question you're asking.Michael J Swart– Michael J Swart2013-06-26 14:09:29 +00:00Commented Jun 26, 2013 at 14:09
-
Other link that may be useful SQL Server LIKE vs CONTAINS.Gaston Flores– Gaston Flores2013-06-26 14:15:40 +00:00Commented Jun 26, 2013 at 14:15
-
When using TEXT as raw SQL, make sure it's escaped, preferably using prepared statements.Cees Timmerman– Cees Timmerman2013-12-16 12:52:37 +00:00Commented Dec 16, 2013 at 12:52
Add a comment
|
6 Answers
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]%'
1 Comment
Hart CO
ISNUMERIC returns 1/0, would have to be
WHERE ISNUMERIC(STUDENTID) <> 1Just try below script:
Below code works only if studentid column datatype is varchar
SELECT * FROM STUDENTS WHERE STUDENTID like '%Searchstring%'
Comments
Try LIKE construction, e.g. (assuming StudentId is of type Char, VarChar etc.)
select *
from Students
where StudentId like '%' || TEXT || '%' -- <- TEXT - text to contain
answered Jun 26, 2013 at 14:04
Dmitrii Bychenko
188k20 gold badges178 silver badges231 bronze badges
1 Comment
Hans Kesting
SqlServer uses a
+ as string concat operator, not a ||. msdn.microsoft.com/en-us/library/ms190301.aspxriffing from bgs, please upvote them first.
I just wanted to expand on it
SELECT * FROM STUDENTS WHERE STUDENTID == 'Searchstring'
Will ONLY find Searchstring
SELECT * FROM STUDENTS WHERE STUDENTID like '%Searchstring%'
will find
1 Searchstring 1
2 Searchstring 2
3Searchstring3
etc Searchstring etc
SELECT * FROM STUDENTS WHERE STUDENTID like 'Searchstring%'
will find
Searchstring 1
Searchstring 2
SearchstringEtc
Will not find
1 Searchstring 1
or any prefixes at all
In this case % is used kinda the same as the same as the * wildcard, just for strings in this case.
Comments
Try this:
SElECT * FROM STUDENTS WHERE LEN(CAST(STUDENTID AS VARCHAR)) > 0
With this you get the rows where STUDENTID contains text
Comments
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.
