CODEWITHSUNDEEP
sql-server
user1345246
user1345246

Reputation: 145

Simple SQL query won't work when using an in clause

Field1 has value 183872 in it but the following query doesn't return anything

select * 
from table1 
where field1 in ('Abbott,Christina D - 183872')

Upvotes: 1

Views: 52

Answers (4)

Brian
Brian

Reputation: 7289

As mentioned in another answer IN is used to test against a set of values.

SELECT * FROM CUSTOMERS
WHERE COUNTRY IN ('USA','CANADA','MEXICO');

You can use CHARINDEX() to find the position of a string within another string. 

select * from table1 
where CHARINDEX(field1,'Abbott,Christina D - 183872') > 0

Upvotes: 0

Brian Kalski
Brian Kalski

Reputation: 957

For the query to work correctly it would have to be something like 

select * 
from table1 
where field1 in ('183872','22244','2224455')

You could do something like the following

select * 
from table1 
where field1 like '%183872%'

That would bring back all records with 183872 in it.

Upvotes: 1

Scott Isaacs
Scott Isaacs

Reputation: 1168

That isn't what in is for. You need to use like. Also, is field1 a numeric field? If so, you should need to cast/convert to string:

select * 
from table1 
where 'Abbott,Christina D - 183872' like '%' + cast(field1 as varchar(10)) + '%'

Upvotes: 4

user359040
user359040

Reputation:

That's because in doesn't mean "value is contained within the following string", but instead means "value is contained within the following set of values".

SQLServer is working correctly.

Upvotes: 3

Related Questions