Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


search values in a temp table with like operator


search values in a temp table with like operator

Author
Message
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
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
   where   firstname 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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18081
You should really try the 8K Splitter. 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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
Thanks Luis! I have to practice and read carefully as CTE is rather confusing for me Wink... 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
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6661
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?

Susanne
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6661
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
Great!! This works out! So if I cannot find out how CTE will work, I can do it with this solution!
Thanks to all!! :-D

Susanne
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search