SQL declared variable as nvarchar. Use dec variable as 'like' or 'Contains' in where clause

Question

I have declared my variable:

declare @search as nvarchar
set @search = 'road'

Then I effectively want to have this in my Where clause:

where  

unit.BuildingName like '%@search%' or
property.Street like '%@search%' or
unit.Street like '%@search%'

Obviously not going to work as this isn't even looking at my declared variable.

But can you see what I am trying to get at?

Thanks!

Possible duplicate of [T-SQL and the WHERE LIKE %Parameter% clause](https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause) — Tab Alleman, Sep 19 '17 at 12:34

score 1 · Accepted Answer · answered Sep 19 '17 at 12:45

1

You should change the query like this.

declare @search as nvarchar(500)
set @search = '%road%'

where  
unit.BuildingName like @search or
property.Street like @search or
unit.Street like @search

answered Sep 19 '17 at 12:45

Serkan Arslan

12,740
4
28
42

score 0 · Answer 2 · answered Sep 19 '17 at 12:29

0

@search in your query is being interpreted literally, instead of as a variable value. You probably want this:

where  

unit.BuildingName like '%' + @search + '%' or
property.Street like '%' + @search + '%' or
unit.Street like '%' + @search + '%'

answered Sep 19 '17 at 12:29

James

3,063
1
24
34

score 0 · Answer 3 · answered Sep 19 '17 at 12:32

If I'm guessing that you are using SQL Server (based on the reference to contains), you need to do two things:

declare @search as nvarchar(255)
set @search = 'road';

Note the addition of a length.

Then:

where unit.BuildingName like '%' + @search + '%' or
      property.Street like '%' + @search + '%' or
      unit.Street like '%' + @search + '%'

If this is part of dynamic SQL, you should be passing @search in as a string, rather than munging the query string.

You might be able to simplify your logic to:

where (unit.BuildingName + property.Street + unit.Street) like '%' + @search + '%'

SQL declared variable as nvarchar. Use dec variable as 'like' or 'Contains' in where clause

3 Answers3