search values in a temp table with like operator

  • Hi,

    I have a SQL proc with a search-parameter (given as coma separated values) which should return all data of a table in which one of the search criteria is valid to any field of the table. At the moment I compare with '=' but I have to change this into like operator. No idea how I can handle this. Help would be highly appreciated!

    Thanks!!!

    Sue

    create table tblclient

    (ID int identity(1,1) primary key,

    Firstname varchar(50),

    Lastname varchar(50),

    birthdate smalldatetime

    )

    go

    insert into tblclient

    values('John','Singer','01.04.1980'),

    ('Mary','Smith','21.06.1975'),

    ('Marylou','Singersmith','11.03.1987'),

    ('Carl','Smith','11.03.1987')

    go

    create proc pSearch

    @searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980

    as

    set @searchparam = @searchparam + ','

    declare @value varchar(100)

    set @value = ''

    -- create temp table to enter search values separated in rows

    create table #search(value varchar(100))

    -- insert each value into #search

    while charindex(',',@searchparam)> 0

    begin

    set @value = left(@searchparam, charindex(',',@searchparam)-1)

    set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam)))

    insert into #search values(@value)

    end

    -- return all clients which referes to either one of the search criterias with like operator

    select id, firstname, lastname, birthdate

    from tblclient

    wherefirstname in(select value from #search)

    or lastname in(select value from #search)

    or convert(varchar(15),birthdate,104) in(select value from #search)

    go

    exec pSearch 'Mary' -- should return mary and marylou

    exec pSearch 'Mary, Smit' -- should return mary, marylou and carl

    exec pSearch 'Mary, 11.03.1987' -- should return mary,marylou and carl

    Susanne

  • You should really try the 8K Splitter[/url]. However, your code won't perform great because SQL Server might not generate the best plan for your query.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • In addition to what Luis posted you need to check out this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

    It explains how to avoid some major performance issues with search type queries.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Luis! I have to practice and read carefully as CTE is rather confusing for me ;)... but you think this will solve the problem?

    Performance is not the point as in the client table will not be more than 1000 rows.

    Thanks!

    Sue

    Susanne

  • Thanks Sean! But I cannot see how dynamic SQL will help here? The problem is that all fields have to be checked for the search criteria so I cannot put together a dynamic SQL clause. Maybe you can clarify?

    Thank you!

    Susanne

  • Something like this should be very close at least, I think:

    create proc pSearch

    @searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980

    as

    set @searchparam = @searchparam + ','

    declare @value varchar(100)

    set @value = ''

    -- create temp table to enter search values separated in rows

    create table #search(value varchar(100) primary key)

    -- insert each unique value into #search

    while charindex(',',@searchparam)> 0

    begin

    set @value = left(@searchparam, charindex(',',@searchparam)-1)

    set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam)))

    insert into #search select @value where not exists ( select 1 from #search where value = @value )

    end

    UPDATE STATISTICS #search WITH FULLSCAN

    -- return all clients which match any one of the search criteria with like operator

    select distinct c.id, c.firstname, c.lastname, c.birthdate

    from dbo.tblclient c

    inner join #search s on

    c.Firstname like s.value

    or c.Lastname like s.value

    or c.birthdate like s.value

    go

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?

    Susanne

  • Sue-651097 (7/22/2013)


    But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?

    Yes you can still do a wildcard search.

    inner join #search s on

    c.Firstname like s.value + '%'

    or c.Lastname like s.value + '%'

    or c.birthdate like s.value + '%'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (7/22/2013)


    Sue-651097 (7/22/2013)


    But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?

    Yes you can still do a wildcard search.

    inner join #search s on

    c.Firstname like s.value + '%'

    or c.Lastname like s.value + '%'

    or c.birthdate like s.value + '%'

    Yes, sorry, typo, I left off the " + '%' ".

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Great!! This works out! So if I cannot find out how CTE will work, I can do it with this solution!

    Thanks to all!! 😀

    Susanne

  • You don't need a CTE. In this case, it would just add complexity for no real reason that I can see :-).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Scott,

    I believe that Sue is mentioning the CTE that's included in the 8K Splitter that I mentioned.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Exactly! But thanks to all of you!! Great help!:kiss:

    Susanne

Viewing 13 posts - 1 through 12 (of 12 total)

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