Check if a column contains text using SQL

Question

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

Check this [link](http://stackoverflow.com/questions/295266/full-text-catalog-index-search-for-book). I assume that if you use **CONTAINS**, you are using Full text catalog. — Gaston Flores, Jun 26 '13 at 14:08
Are 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, Jun 26 '13 at 14:09
Other link that may be useful [SQL Server LIKE vs CONTAINS](http://stackoverflow.com/a/7510685/1563878). — Gaston Flores, Jun 26 '13 at 14:15
When using TEXT as raw SQL, make sure it's escaped, preferably using prepared statements. — Cees Timmerman, Dec 16 '13 at 12:52

Michael J Swart · Accepted Answer · 2017-03-02T15:25:11.477

50

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]%'

edited Mar 02 '17 at 15:25

answered Jun 26 '13 at 14:06

Michael J Swart

3,060
3
29
46

ISNUMERIC returns 1/0, would have to be `WHERE ISNUMERIC(STUDENTID) <> 1` – Hart CO Jun 26 '13 at 14:09

score 27 · Answer 2 · answered Jun 26 '13 at 14:04

27

Just try below script:

Below code works only if studentid column datatype is varchar

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

answered Jun 26 '13 at 14:04

bgs

3,061
7
40
58

Dmitry Bychenko · Answer 3 · 2020-08-10T14:14:02.120

14

Try LIKE construction, e.g. (assuming StudentId is of type Char, VarChar etc.)

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

edited Aug 10 '20 at 14:14

answered Jun 26 '13 at 14:04

Dmitry Bychenko

180,369
20
160
215

8

SqlServer uses a `+` as string concat operator, not a `||`. https://msdn.microsoft.com/en-us/library/ms190301.aspx – Hans Keﬆing Jul 07 '15 at 12:57

score 2 · Answer 4 · answered Jun 26 '13 at 14:37

2

Try this:

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

With this you get the rows where STUDENTID contains text

answered Jun 26 '13 at 14:37

jhonatan299

21
2

score 1 · Answer 5 · answered Feb 11 '22 at 12:57

riffing 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.

score -2 · Answer 6 · edited Oct 02 '19 at 11:21

-2

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.

edited Oct 02 '19 at 11:21

Cleptus

3,446
4
28
34

answered Oct 02 '19 at 09:29

Sunil Jadhav

127
1
5

Check if a column contains text using SQL

6 Answers6

Linked