CODEWITHSUNDEEP
databasesql-server-2008
Jibin Balachandran
Jibin Balachandran

Reputation: 3441

How to use LIKE and OR together in a Query

The following is my code for altering a procedure which will search for a book name or book author:

ALTER PROCEDURE [dbo].[SearchBook]

@Search_Var varchar(50)

AS
BEGIN

SELECT * 
FROM Book 
WHERE 
BookName LIKE '%@Search_Var%' OR 
BookAuthor LIKE '%@Search_Var%'

END

I want it to display books by matching substring also i.e if book name is Fundamental Chemistry by typing chemistry also i should be able to get the result. But the above code is not working properly.

Upvotes: 1

Views: 39

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You need to concatenate searched phrase like:

ALTER PROCEDURE [dbo].[SearchBook]
   @Search_Var VARCHAR(50)
AS
BEGIN
  SELECT * 
  FROM Book 
  WHERE 
    BookName LIKE '%' + @Search_Var + '%' 
    OR BookAuthor LIKE '%' + @Search_Var + '%';
END

Keep in mind that searching using '%phrase%' is not SARG-able and Query Optimizer won't use indexes if exists any.

Upvotes: 2

Related Questions