search values in a temp table with like operator

Question

search values in a temp table with like operator

Sue-651097

Say Hey Kid

Points: 675
More actions
July 22, 2013 at 8:57 am

#395178

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 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/

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 3

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

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 4

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 SSC Guru Points: 101099 More actions · Answer 5

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

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 6

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

Susanne

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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/

ScottPletcher SSC Guru Points: 101099 More actions · Answer 8

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

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 9

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 10

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 12

Exactly! But thanks to all of you!! Great help!:kiss:

Susanne