CODEWITHSUNDEEP
t-sqlsql-like
Hajitsu
Hajitsu

Reputation: 784

use IN clause and Like in T-SQL

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.

Upvotes: 0

Views: 1476

Answers (2)

Jude Wood
Jude Wood

Reputation: 76

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

Upvotes: 1

Randy
Randy

Reputation: 16677

probably drop the quotes:

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

Upvotes: 0

Related Questions