Click here to Skip to main content
Click here to Skip to main content

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

, 1 Mar 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Way to search record in relational tables to get search count on a search string

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Prasad Bhalekar(PP)
Architect
India India
Myself from the dream city of Mumbai,India.
Academically, I have completed Degree and Diploma in Computer Science engineering
 
I started working as a freelance programmer, on fronts like embedded systems, web, windows , using Microsoft as well as Sun technologies.
 
Presently working in a reputed IT company.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 1 Mar 2008
Article Copyright 2008 by Prasad Bhalekar(PP)
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid