Simple way to implement database driven Advance Search Engine to search data in many tables

Introduction

When we want data to search from many related tables of data base we will create one view, to search data for all related tables, and apply search string on that view to get result as sum of search word match table name with index field and it’s value

Background

To get information from database from many related table we require to query on each table to fetch record, where we do not know witch table or relation have the search data and we want search to find table, relation as well as record from database we will used this.

Using the code

We will create one view to hold all related table and data for search

View - SearchData

SELECT LastName + FirstName + NickName AS SearchText,
PersonId AS Id, 'Person' AS TableName, 'PersonId ' AS IdField
FROM dbo.Person
UNION
SELECT FirstName + LastName AS SearchText, DOCTORId AS Id, 'DOCTOR' AS TableName, 'DOCTORId' AS IdField
FROM dbo.DOCTOR

SearchText : contain fields of table to search data

Id : Contain value of index field

TableName: Contain table name

IdField : Contain field name for find record of related table using Id value

Fuction to create Table from search string

function [dbo].[String_Split](@String nvarchar (4000),@Delimiter nvarchar (10)) returns ValueTable table ([Value] nvarchar(4000))

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

set @NextString = ''

set @CommaCheck = right(@String,1)

set @String = @String + @Delimiter

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

while (@pos <> 0)

begin

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

return

end

To get search data

PROCEDURE [dbo].[AdvanceSearch] @searchtring varchar(1000)
AS
BEGIN

--declare @searchtring varchar(1000)
--set @searchtring = 'wi,jo'

set @searchtring = replace(@searchtring,',',' ')
set @searchtring = replace(@searchtring,';',' ')
set @searchtring = replace(@searchtring,'.',' ')
--set @searchtring = replace(@searchtring,'.',' ')

--select '%' + value + '%' as Keys from String_Split( @searchtring ,' ')

SELECT count(Id) as MatchFildsCount , SearchText, Id, TableName, IdField
FROM SearchData join
(select '%' + value + '%' as Keys from String_Split( @searchtring ,' ')) searchKeyTable
on SearchText like Keys
group by SearchText, Id, TableName, IdField
order by count(Id)desc

END

e.g.

AdvanceSearch N'wi,jo,sir'

MatchFildsCount	SearchText	Id	TableName	IdField
2	BradmanJohnSir	3	Person	PersonId
2	WilliamsJohn	5	Person	PersonId
1	WinstonChurchillWinky	45	Person	PersonId
1	CookJoanJoan	34	Person	PersonId
1	JohnriedMan	3	DOCTOR	DOCTORId
1	JonesDeanJon	35	Person	PersonId
1	JonesMariaMaria	43	Person	PersonId
1	LeoWilliams	28	DOCTOR	DOCTORId
1	LeoWilliams	30	DOCTOR	DOCTORId
1	LeoWilliams	31	DOCTOR	DOCTORId
1	LeoWilliams	32	DOCTOR	DOCTORId
1	TaylorJonesjones	30	Person	PersonId
1	WilliamsHarryHarry	59	Person	PersonId