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!

Answer 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

Answer 2

@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 + '%'

Answer 3

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 + '%'