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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy