August 4, 2008 at 7:28 am
HI,
i want to make a query that search multiple keyword with like operator,
ex. select * from employee where name like '%pankaj%'
but i want that it search many key word. user can give any number of parameter.
August 4, 2008 at 7:37 am
SELECT *
FROM Employee
WHERE [Name] LIKE '%' + @vcSearch1 + '%'
OR [Name] LIKE '%' + @vcSearch2 + '%'
OR [Name] LIKE '%' + @vcSearch3 + '%'
etc
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 4, 2008 at 7:40 am
But user can give any parameter then how it will search.
August 4, 2008 at 7:50 am
here is an example of how it will work.
DECLARE @vcSearch1 VARCHAR(100)
DECLARE @vcSearch2 VARCHAR(100)
DECLARE @vcSearch3 VARCHAR(100)
DECLARE @tbl TABLE
([Name] VARCHAR(1000))
INSERT INTO @tbl
SELECT 'Christopher Stobbs' UNION ALL
SELECT 'pkuchaliya' UNION ALL
SELECT 'Jack Smith'
--GET ALL ROWS
SELECT [Name] FROM @tbl
--Use Search Data
-- 1 Value
SELECT
@vcSearch1 = 'Christopher',
@vcSearch2 = NULL, --Nothing passed in
@vcSearch3 = NULL --Nothing passed in
SELECT [Name]
FROM @tbl
WHERE [Name] LIKE '%' + ISNULL(@vcSearch1,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch2,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch3,'NO DATA') + '%'
-- 2 Values
SELECT
@vcSearch1 = 'Christopher',
@vcSearch2 = 'uch', --Nothing passed in
@vcSearch3 = NULL --Nothing passed in
SELECT [Name]
FROM @tbl
WHERE [Name] LIKE '%' + ISNULL(@vcSearch1,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch2,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch3,'NO DATA') + '%'
-- 3 Values
SELECT
@vcSearch1 = 'Christopher',
@vcSearch2 = 'uch', --Nothing passed in
@vcSearch3 = 'Smith' --Nothing passed in
SELECT [Name]
FROM @tbl
WHERE [Name] LIKE '%' + ISNULL(@vcSearch1,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch2,'NO DATA') + '%'
OR [Name] LIKE '%' + ISNULL(@vcSearch3,'NO DATA') + '%'
Or do you mean that they could search on Name or Address etc?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 4, 2008 at 7:57 am
Actually sir my problem is that i have to pass parameter from user interface.
suppose user enter anudeep,pankaj,description,sanjeet.
then it will all these .
so how i can identify that how many object i have to create.
August 4, 2008 at 8:00 am
What is the max number of search criteria?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 4, 2008 at 8:07 am
The search criteria can be any number .
August 4, 2008 at 8:18 am
Ok I got a solution for you!
Please not the proc requires a Tally table if you don't have one the code for it is commented out in the beginning of the proc.
CREATE PROCEDURE dbo.MyProc
(@vcSearch VARCHAR(8000))
AS
--This Procedure Requires a Tally Table
/*
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
*/
--DELIMIT YOUR STRING TO A TABLE.
;WITH MyCTE (Search)
AS
(SELECT TOP 1000 '%' + SUBSTRING(@vcSearch+',', n,
CHARINDEX(',', @vcSearch+',', n) - n) + '%'
FROM dbo.Tally
WHERE n <= LEN(@vcSearch)
AND SUBSTRING(',' + @vcSearch,
n, 1) = ','
ORDER BY n)
--USER THE TABLE TO SEARCH YOUR TABLE
SELECT t.*
FROM [Help_DishaSiteMap] t
INNER JOIN MyCTE cte
ON [description] like cte.Search
RETURN
GO
EXEC dbo.MyProc 'anudeep,temp'
The input param to the proc is a , seperated file. so you can pass 'anudeep' or 'anudeep,temp,Chris,whateva'.
AS long you you put a comma between each search item.
This proc only searches the description column I think that is what you were looking for.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply