Member Avatar for hwoarang69

I need some help with creating a simple search engine for website. Basic idea is that user will enter a string in search bar, which will compare in database key_word and get the reuslts.

Lets say I have the following table on sql server database.

   |----|----------|----------------------|
    | ID | URL      | key_word             |
    |----|----------|----------------------|
    | 1  | url1.com | cat short red NYC    |
    | 2  | url2.com | tall blue LA         |
    | 3  | url3.com | skinny NYC green     |
    | 4  | url4.com | cat black get        |
    |----|----------|----------------------|

Now in search bar, lets say user want to search the below string "get red cat from NYC". I want to search this in database 'key_word'.

       String key = "get red cat from NYC"
What I have tried:

So far I have the following below query to search from database. This is good for if user want to search for only one word. but the string 'key' will not work here and it will return 0 result. I need some idea so I can make this better query.

        SELECT * FROM [SearchTable] WHERE [key_Word] LIKE % key %;
What I want:

I want to change this sql server query so that it return ID=1,3,4.

So in other words. I want to take this string:

    String key = "get red cat from NYC"

and first search in database the word "get". it doesn't show up so go to next word. Next word is "red", this shows up in ID=1. next word is "cat", this shows up in ID=1,4. Next word is "from", this doesn't show up in any rows. Next word is "NYC", this shows up in ID=1,3.

put all id's together and you get ID's=1,1,4,1,3.

than I want to sort it so that ID=1 shows up at top and ID=3,4 can be at button since they are tied.

I was hoping to do this by only one sql query, bc if I keep connecting to database than the speed will go down tooo. So I was think of some sql server funcctions?

  • 3 Contributors
  • 2 Replies
  • 1K Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

the simplest way to do this in one round trip and auto-rank the best matches is SQL Server Full-Text Search (FTS), not LIKE. FTS tokenizes your key_word text, ignores common stopwords like "from", and returns a relevance score (RANK). Rows that match more of the query terms naturally float to the top, which is exactly how you want ID 1 ahead of 3 and 4. See the official notes on stopwords and the FTS rowset functions (CONTAINSTABLE/FREETEXTTABLE). Configure stopwords and stoplists, Full-Text Search functions. (learn.microsoft.com)

Quick start (one-time setup and a single query):

-- one-time setup
CREATE FULLTEXT CATALOG WebSearch;
CREATE FULLTEXT INDEX ON dbo.SearchTable(key_word LANGUAGE 1033)
KEY INDEX PK_SearchTable
ON WebSearch
WITH CHANGE_TRACKING AUTO;  -- use your table's unique index name
-- query
DECLARE @q nvarchar(4000) = N'get red cat from NYC';

SELECT s.ID, s.URL, s.key_word, k.RANK
FROM FREETEXTTABLE(dbo.SearchTable, key_word, @q) AS k
JOIN dbo.SearchTable AS s
  ON s.ID = k.[KEY]
ORDER BY k.RANK DESC, s.ID;

FREETEXTTABLE splits the phrase, drops stopwords, and returns [KEY] (your row id) plus RANK, so you get a single, ranked result set. Docs: CREATE FULLTEXT INDEX, FREETEXTTABLE. (learn.microsoft.com)

is right that OR-ing many LIKEs gets unwieldy. If you later normalize as suggests (one keyword per row in a link table with an index on term), you can also score results with a simple GROUP BY/COUNT(*) and order by the match count. FTS will still be faster and gives ranking, proximity, and linguistic matching out of the box. For large datasets, keep using FTS and its RANK to sort. FTS performance guidance. (learn.microsoft.com)

Recommended Answers

All 2 Replies

Member Avatar for hericles

You can do one search but you'll need to add in WHERE clauses, one for each word in the search string. E.g.
SELECT * FROM [SearchTable] WHERE [key_Word] LIKE '%get%' OR [key_Word] LIKE '%red%' OR [key_Word] LIKE '%cat%';
That can get reasonably big when searching for a lot of words of course.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.