CODEWITHSUNDEEP
t-sqlsearchstored-proceduresdynamic
feronovak
feronovak

Reputation: 2697

Creating dynamic search using T-SQL stored procedures

Is there a way to create search based on number of words?

For example if I search for car and wheel it would create T-SQL search like

select * from table 
where
    (word is like @word1)
    and (word is like @word2)

where @word1 is car and @word2 is wheel.

It could be any number of words, therefore this should be dynamic.

Thanks.

Upvotes: 1

Views: 450

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44356

Try this, @wordtable can dynamically contain all the words you want. and it will only find the rows from @table where all words exists:

declare @wordtable table(word varchar(20))
declare @table table(word varchar(500))

insert @wordtable values('car')
insert @wordtable values('wheel')

insert @table values('carwheel')
insert @table values('car')
insert @table values('wheel')
insert @table values('wheelcat')

select * from @table t
where not exists (select 1 from @wordtable where t.word not like '%' + word + '%')

"Contains" would proberly still be better. But this is a decent way of solving the problem.

Upvotes: 1

alphacoder
alphacoder

Reputation: 21

I would put into consideration what has been said above , however if you still would like to write sql for this , you could try the CHARINDEX function

declare @searchTerms  varchar(100)
select  @searchTerms = 'WORD1,WORD2,GIRLS,BOYS'  -- some list

select  Field1, Field2 , Field3 , Field4
from    SomeTable
where   CHARINDEX( ',' + Field1 + ',', ',' + @searchTerms + ',' , 0 ) > 0

So your searchTerms , Table and Field can be replaced by variables if you wish , then you build a sql statement which you then execute

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29649

Assuming it really is words you want to search for, SQL Server has built-in full text search (http://msdn.microsoft.com/en-us/library/ms142571.aspx) - it's been around for at least 8 years. It's not perfect - and you could make a case for Lucene being better - but if your needs are simple, it certainly does the job.

It also means you can just use off the shelf functionality for text searches, such as "fuzzy" matching.

As you won't have as much code to write, you'll avoid the bug in your pseudo code - 

(word is like @word1)
    and (word is like @word2)

will only yield a result if word1 and word2 are the same...

Upvotes: 1

Petar Ivanov
Petar Ivanov

Reputation: 93090

This would be extremely slow and not scalable. Why don't you consider using something like Lucene.

Upvotes: 0

Related Questions