June 1, 2009 at 12:27 am
/*
This is My Seacrh Criteria
USER ENTERSSYSTEM LOOKS FOREXAMPLE RESULTS
Chapter 1Exact matches within a fieldChapter 1 The Human Body
"Chapter 1"Exact matches – entire fieldChapter 1
"Chapter 1"*Matches beginning with “Chapter 1”Chapter 1 The Human Body
Chapter 10 Invertebrate Animals
Chapter 100 The Schedule Time
*Chapter 1Matches ending with “Chapter 1”Dinosaurs: The Final Chapter 1
Chapter 1*Matches beginning with “Chapter 1”Chapter 1 The Human Body
Chapter 1 Invertebrate Animals
*/
/*
CREATE TABLE #tbl
(
ID INT IDENTITY(1,1),
Name NVARCHAR(MAX)
)
INSERT INTO #tbl(Name) VALUES ('Chapter 1 The Human Body')
INSERT INTO #tbl(Name) VALUES ('Chapter 1')
INSERT INTO #tbl(Name) VALUES ('Chapter 10 Invertebrate Animals')
INSERT INTO #tbl(Name) VALUES ('Chapter 1 Invertebrate Animals')
INSERT INTO #tbl(Name) VALUES ('Dinosaurs: The Final Chapter 1')
INSERT INTO #tbl(Name) VALUES ('Chapter 100 The Schedule Time')
INSERT INTO #tbl(Name) VALUES ('Chapter 20 The IpAddress and Net Mask')
INSERT INTO #tbl(Name) VALUES ('Lesson 1 The Graphics DDA Alogrithm')
INSERT INTO #tbl(Name) VALUES ('Lesson 2 The Graphics Bresenham Alogrithm')
Select * From #tbl
DROP TABLE #tbl -- Clean Up
*/
June 1, 2009 at 12:58 am
Hi,
try this
declare @option varchar(2),
@search varchar(100)
select @option = 'EX'--for Exact matches
--or 'BE' for beginning with
--or 'EN' for ending with
select @search = 'Chapter 1'
select * from #tbl
where Name like (case when @option = 'EX'then @search
when @option = 'BE'then ('%'+@search)
when @option = 'EN'then (@search+'%')end)
/*IF required add this filter */
and Name (case when @option in('BE','EN')then @search else ''end)
ARUN SAS
June 1, 2009 at 6:33 am
I would suggest using more than one column to store the data, then your users can search intelligently.
create table dbo.Chapters
(chapterID int not null,
sectionID int not null,
csDescription varchar(100) not null
)
insert into dbo.Chapters
select 1,1,'Introduction'
union select 1,2,'Chapter One'
union select 2,1,'Chapter Two - Something About Dinosaurs'
union select 2,2,'Chapter Two - Something About Trees'
union select 2,3,'Chapter Two - Something About Fish'
union select 3,1,'Something About Algae'
union select 4,1,'Something About Fungi'
union select 4,2,'Something About Bacteria'
union select 4,3,'Something About Slimes & Moulds'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply