Loading ...

Advance search in sql | CodeAsp.Net

Advance search in sql

 /5
0 (0votes)

In this blog I will explain how to perform advance search in sql.In many applications advance search is required where user enters keywords and on basis of that keywords records from the database are fetched. User can enter multiple keywords and on basis of that we have to perform all of these ,or any of these keywords search. Below is the script for that

 

Go

--Any Of These With Exact Word Search Without Considering The Order Of KeyWords

create table searchwords

(

myword varchar(100)

)

insert into searchwords values ('Best day')

insert into searchwords values ('Book big')

insert into searchwords values ('Big India Data')

insert into searchwords values ('Hi Sql')

insert into searchwords values ('Database')

declare @searchwords varchar(100)

set @searchwords = 'Best Book India Database'

select distinct myword from (select * from searchwords s

cross apply fn_split(s.myword,' ') as d )t

where charindex(value,@searchwords) > 0

--Output

Best day

Big India Data

Book big

Database

Go

--All Of These With Exact Word Search Without Considering The Order Of KeyWords

Go

create table SearchFields

(

SearchID int identity(1,1),

SearchCategories varchar(100)

)

insert into SearchFields values ('abc bbb nnn sql')

insert into SearchFields values ('Book India')

insert into SearchFields values ('Doll Database Red Yellow Carpet Give')

insert into SearchFields values ('Well Done Hello')

declare @searchIDs varchar(100)

set @searchIDs = 'Database Doll Red Yellow Carpet Give'

;with cte as

(select searchId from

( select SearchID,SUBSTRING(' ' + s.SearchCategories + ' ', n.Number + 1, CHARINDEX(' ', ' ' + s.SearchCategories + ' ', n.Number + 1) - n.Number - 1) as colname

from SearchFields s

inner join master..spt_values n on substring(' ' + s.SearchCategories + ' ',n.Number,1) = ' '

where n.type = 'p' and n.number > 0 and n.Number < LEN(' ' + s.SearchCategories + ' ')

)t

where charindex(' '+colname+' ',' '+ @searchIDs + ' ') > 0)

select distinct s.* from cte c

inner join SearchFields s on c.SearchID = s.SearchID

where (select count(*) from cte where SearchId = S.SearchId) =

(len(@searchIDs) - len(replace(@searchIDs,' ','')) + 1)

Go

--Output

3 Doll Database Red Yellow Carpet Give

--Any Of These With Like Condition of Word Search Without Considering The Order Of KeyWords

declare @searchIDs varchar(100)

set @searchIDs = 'Doll Red Give Book'

;with cte as

(

select SearchID,count(SearchId) as Rank from

(

select SearchID from SearchFields S

cross apply fn_split(s.SearchCategories,' ') as D

cross apply fn_split(@searchIDs,' ')as t

where d.value like '%' + t.value + '%' or t.value like '%' + d.value + '%'

)t

group by SearchID

)

--select * from cte

select f.*,rank from cte c inner join searchfields f

on c.searchId = f.searchID

order by rank desc

Go

--Output

3 Doll Database Red Yellow Carpet Give 3

2 Book India 1

--All Of These With Like Condition of Word Search Without Considering The Order Of KeyWords

declare @searchIDs varchar(100)

set @searchIDs = 'Wel Don Hello'

;with cte as

(

select SearchID,count(SearchId) as Rank from

(

select SearchID from SearchFields S

cross apply fn_split(s.SearchCategories,' ') as D

cross apply fn_split(@searchIDs,' ')as t

where d.value like '%' + t.value + '%' or t.value like '%' + d.value + '%'

)t

group by SearchID

)

--select * from cte

select f.*,rank from cte c inner join searchfields f

on c.searchId = f.searchID

where rank >= ( len(@searchIDs) - len(replace(@searchIDs,' ','')) + 1)

order by rank desc

Go

--Output

4 Well Done Hello 3

 

 

 

Happy Reading!

 

Comments (no comments yet)

Top Posts