T-SQL search through all columns for jquery autocomplete textbox

Question

I have been trying to design a query to search for specific text through all columns but it is not working as required.

Here is my query:

create table #tblTempAddress (locationId int identity(1,1) primary key, postcode nvarchar(300), road nvarchar(1000), ApartmentName nvarchar(1000), district nvarchar(200), Village nvarchar(200), City nvarchar(200))

insert into #tblTempAddress values ('DY4 8QJ','Union Street',NULL,'Tipton',NULL,NULL)
insert into #tblTempAddress values ('DY4 9JP','Phillips Court','Union Street','Princes End',NULL,NULL)
insert into #tblTempAddress values ('DY4 9JR','Union Street','Princes End','Tipton',NULL,NULL)
insert into #tblTempAddress values ('DY8 1PJ','Union Street',NULL,'Stourbridge',NULL,NULL)
insert into #tblTempAddress values ('DY8 1PR','Union Street',NULL,'Stourbridge',NULL,NULL)
insert into #tblTempAddress values ('DY9 8BJ','Union Street','Lye','Stourbridge',NULL,NULL)
insert into #tblTempAddress values ('B65 0EL','Union Street',NULL,'Rowley Regis',NULL,NULL)
insert into #tblTempAddress values ('B65 0ER','Union Street',NULL,'Rowley Regis',NULL,NULL)
insert into #tblTempAddress values ('DY2 8PJ','Union Street',NULL,'Dudley',NULL,NULL)
insert into #tblTempAddress values ('DY2 8PP','Union Street',NULL,'Dudley',NULL,NULL)


declare @searchtext nvarchar(1000)
set @searchtext = 'union street'
select top 10 postcode, road, ApartmentName, District, Village, City from #tblTempAddress    
where road like '%' + @searchtext + '%'        
OR ApartmentName like '%'+ @searchtext + '%'    
OR District like '%'+ @searchtext + '%'    
OR Village like '%'+ @searchtext + '%'    
OR City like '%'+ @searchtext + '%'    
OR postcode like '%' + @searchtext + '%'   

When I set the @searchtext to "union street" it returns all data related to "union street" but when I add "lye" like @searchtext = 'union street lye' then its not showing result related to union street Lye which is on the row number 6. I have gone through this blog but no help.

Answer 1

You can do something like this.

--your table declaration here
declare @searchtext nvarchar(1000)
set @searchtext = 'union street in lye'

;with keywords as (
select value from string_split(@searchtext,' ') --tokenize @searchtext
except
select * from string_split('and or a an the for at on in why how when to from',' ')--exclude stop words
),
kwcount as (
select COUNT(value) total from keywords
),
matches as (
select t.locationId
,COUNT(distinct value) cnt
from #tblTempAddress t
inner join keywords on concat(postcode,' ', road,' ', ApartmentName,' ', district,' ', Village,' ', City) like '%'+keywords.value+'%'
group by t.locationId
)
select top 10 postcode, road, ApartmentName, District, Village, City 
from #tblTempAddress t
inner join matches m on t.locationId=m.locationId
cross join kwcount c
where m.cnt=c.total --require match all keywords in @searchtext. weaker filter may be used
order by m.cnt desc

The query may give some false positives so filter tuning is desired.
Also FullText search could be very helpful.