Query with multiple string with like operator

  • 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.

  • 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]

    SQL-4-Life
  • But user can give any parameter then how it will search.

  • 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]

    SQL-4-Life
  • 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.

  • 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]

    SQL-4-Life
  • The search criteria can be any number .

  • 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]

    SQL-4-Life

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply