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
|
