1

I started by asking this question I wish to Select all rows which contain any element from an array And while my current question is almost identical, it serves a whole different purpose, and so I fear the answer may be different which would obscure the previous one, with that said.

I am working on a search engine type thing. I need to search a title stored in a database to see if it contains any of the words in the search textbox. I plan to then iterate through each of those rows, counting how many words match and ranking the results accordingly. I am sure this has been done many times by now. I just can't seem to figure out how to do it without 

select * from table

then sorting it in c#

Community
  • 1
  • 1
General Grey
  • 3,598
  • 2
  • 25
  • 32
  • 4
    To get anything which can be called "search engine" functionality (and performance) you will need to use something like Microsoft Full text search or Lucene. The performance of the TSQL `LIKE` statement will be bad (especially with multiple search terms). – Filip De Vos May 09 '12 at 19:13
  • so i better start looking into Microsoft Full text search – General Grey May 09 '12 at 19:15
  • @K'Leg Or structure your data differently, so you don't have to use LIKE. Whether this would actually be practical in your case is another matter... – Branko Dimitrijevic May 09 '12 at 19:19
  • 1
    [Lucene.Net](http://incubator.apache.org/lucene.net/) and [some info](http://www.codeproject.com/Articles/224722/hOOt-full-text-search-engine) on how to develop your own search engine – L.B May 09 '12 at 19:27
  • @BrankoDimitrijevic can you explain how I might structure my data differently. – General Grey May 09 '12 at 19:29
  • @K'Leg Split the text into words then store words in the separate table (each word is a single row and contains the word text itself and probably its position within the "parent" text, so the parent text can be reconstructed). – Branko Dimitrijevic May 09 '12 at 19:34
  • Branko... yeah far to complex for my liking, @L.B Thanks I will give that a read. – General Grey May 09 '12 at 19:36

2 Answers2

4

one way in sql server is

push the word in temp table like this

DECLARE @SearchWords TABLE (
  word varchar(30) )

INSERT INTO @SearchWords
        (word)
VALUES  ('Jack')
       ,('Pontiac')
       ,('Bloggs');

than join this table with the actual table 

SELECT a.* FROM table_data a
INNER JOIN @SearchWords b on a.record_desc like '%' + b.word + '%'
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • Can you give me a complete query here, I have never used a temp table. – General Grey May 09 '12 at 19:05
  • @K'Leg this is complete query you nee to push the word in table which you want to search in your table column here in query table_data is name of you table and claumng name record_desc with the name of the column – Pranay Rana May 09 '12 at 19:07
  • Its just the first line you are losing me on. I was hoping this would create a temptable for me but it doesn't so I am getting the error Must declare the table variable "@SearchWords" – General Grey May 09 '12 at 19:11
  • @K'Leg - sorry forgot that but its updated now this will work for you – Pranay Rana May 09 '12 at 19:16
  • This will search all substrings, not just words. @K'Leg is that what you want? – Branko Dimitrijevic May 09 '12 at 19:22
  • I like this answer, but as for adding the Values from c# into sql as a query, is there an easy way of doing that. I know in c# I will have to do a string[] word = Title.Text.Split(' '); That gets me the words, now I need to pass them in, which leaves me in the same boat as ametren's answer. I would have to loop through them and add them that way, which is doable, just looking for something more elegant. – General Grey May 09 '12 at 19:26
  • @BrankoDimitrijevic I actually don't know yet, I intended to remove common words like "a" and "I" "if" etc... – General Grey May 09 '12 at 19:27
  • @K'Leg Yes but do you want a search for "do" to return a "dog"? – Branko Dimitrijevic May 09 '12 at 19:29
  • I understand, while I wouldn't want that, I would want glasses to return sunglasses, which is why I was thinking of removing small common words – General Grey May 09 '12 at 19:30
  • Damn this option seems to also return the same row multiple times if it contains more then one of the values(words), which I might be able to work with for the ranking purpose, but I don't think that was intendd – General Grey May 09 '12 at 19:33
  • ALternatively, use CHARINDEX() instead of "like" . . . "on charindex(b.word, a.record_desc) > 0" (or instr(), depending on the database). – Gordon Linoff May 09 '12 at 19:34
2

You could try something like this: 

SELECT * FROM table WHERE name LIKE '%word1%' OR name LIKE '%word2%';

The % signs are the analogous to * in typical searches.

To take the string and create this query for multiple words you could do something like this:

String inputString = "hello my friend";
String[] wordlist = inputString.split(" ");
String query = "SELECT * FROM table WHERE 0=0 ";
for(String word : wordlist) {
    query += "OR name LIKE '%" + word + "%' "; 
}

The spaces at the end of the strings are important, don't forget those! Also, you'll probably want to sanitize the string before you split it (remove extra punctuation, maybe even remove words like "a" or "the" or "to" etc.)

ametren
  • 2,186
  • 15
  • 19
  • I got the like % thing, but what if they enter 15 words into the textbox, I would have to have that many or statements in my query, which would be possible, just cumbersome. – General Grey May 09 '12 at 18:58
  • You could just build the query by splitting the input string on space and then looping through the results. Or you could run the query 15 times and merge the results. – ametren May 09 '12 at 19:05
  • I was leaning towards running it 15 times (however many words there are) and merging, that just seems really inefficient. I think building the query through a loop is the way to go, so far. Just always seems there is a far me elegant way of doing things than what I think – General Grey May 09 '12 at 19:07
  • I just added some code to the answer that might help. Take care to escape these strings though. – ametren May 09 '12 at 19:09
  • Thanks for that, as of now it's the only viable answer I can use, though for future viewers I imagine you would want to remove punctuation .,?() at the least – General Grey May 09 '12 at 19:12
  • Yeah you'd want to strip out punctuation to avoid SQL injection and also to clean up the search. There are lots of other questions on here that explain how to do that. – ametren May 09 '12 at 19:16
  • While it is not the most elegant answer, I find it is the only one that does what I want. I hope something else comes up, if not this will remain my answer – General Grey May 09 '12 at 19:41