use IN clause and Like in T-SQL

Question

I want to select some data in sql express by IN clause and LIKE but no row returned. my code is here :

SELECT        PointId, PointTitleId, PointTitleName, PointTitleContentText
FROM          PointsTitlesData
WHERE        (PointTitleContentText LIKE '%' + @SearchingClause + '%') 
             AND (PointId IN ('2','3'))

above code don't return any data but when i use only LIKE code return data.

SELECT        PointId, PointTitleId, PointTitleName, PointTitleContentText
FROM          PointsTitlesData
WHERE        (PointTitleContentText LIKE '%' + @SearchingClause + '%')

how can i use both IN and LIKE? Thanks.

are you sure you have records that meet the criteria of the IN? — Taryn
– Taryn, Commented Jul 11, 2012 at 12:06
Well... does the table have any value that is like your search clause and the point id is 2 or 3? — Remus Rusanu
– Remus Rusanu, Commented Jul 11, 2012 at 12:06
Can you show us the data from your table where PointId is 2 and 3. Also, table schema for PointsTitlesData table and version of the SQL Server are you using. — Void Ray
– Void Ray, Commented Jul 11, 2012 at 17:15

Jude Wood · Accepted Answer · 2012-07-11 16:48:09Z

I tried to recreate your scenario with the script below

Declare @SearchingClause nvarchar(20);

Create table PointsTitlesData (Pointid int, PointTitleContentText nvarchar(20));

insert into PointsTitlesData (Pointid, PointTitleContentText)
values ( 1, 'aaamypoint1aaa'), (2, 'bbbmypoint2bbb'),(3,'cccmypoint3ccc');

Set @SearchingClause = 'mypoint2';

SELECT        PointId, PointTitleContentText 
FROM          PointsTitlesData 
WHERE        (PointTitleContentText LIKE '%' + @SearchingClause + '%')  
         AND (PointId IN ('2','3')) 

-- above code don't return any data but when i use only LIKE code return data.

SELECT        PointId, PointTitleContentText 
FROM          PointsTitlesData 
WHERE        (PointTitleContentText LIKE '%' + @SearchingClause + '%')

I got a result for both queries so it looks like you have a data issue. Have you tried

SELECT PointId from PointsTitlesData where ((PointId = 2) or (PointId = 3))

to check the data is there.

Regards Jude

Randy · Accepted Answer · 2012-07-11 12:06:21Z

0

probably drop the quotes:

'2','3' should be 2,3

answered Jul 11, 2012 at 12:06

Randy

16.7k2 gold badges40 silver badges56 bronze badges

4 Comments

Hajitsu Over a year ago

I use 2,3 but no data return yet.

Randy Over a year ago

then it must be a data issue. there must be a record that matches both these criteria at the same time

Hajitsu Over a year ago

the code is correct. problem is from janus checked combobox. thanks.

paparazzo Over a year ago

@1mohammadi.ir What does "problem is from janus checked combobox" mean? If it is fixed then update or delete the post so people don't spend time trying to answer it.

Collectives™ on Stack Overflow

use IN clause and Like in T-SQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related