CODEWITHSUNDEEP
sql-serversearchrows
Dashy Khan
Dashy Khan

Reputation: 43

SQL Server search millions of rows in a table

Is it possible without using full-text indexing, can we search huge table on basis of keywords?

I want to search term Country man working and it returns following results:

  1. Country man started working
  2. Country on brink of failure
  3. Man started his job

Upvotes: 0

Views: 243

Answers (2)

Treborbob
Treborbob

Reputation: 1221

Create a table to contain all words in the dictionary, minus noise words.

Create a table to link dictionary keyword table to your data table

Write some code to create records in the link table where a words in the dictionary appear in your text column in the table you want to search.

Then you can search a bit like this:

SELECT T.TextStuff
FROM KeywordTable AS K
JOIN LinkTable AS L ON L.KeywordID = K.KeywordID
JOIN TableToSearch AS T ON T.KeyWordID = L.KeywordID
WHERE K.Keyword = 'Country'
OR K.Keyword = 'man'
OR K.Keyword = 'working'

Thats only pseudo code, but hopefully it'll demonstrate the idea. Time consuming to setup, but should be a better performer than doing a bunch of LIKE's.

To sum up, you want to look in a table where you have one word per row, the ones that match you want to find in the link table, and from there you can get to the record in your original table.

Upvotes: 1

Paddy
Paddy

Reputation: 33857

This should return your required results:

SELECT *
FROM TABLE
WHERE Field LIKE '%country%'
OR Field LIKE '%man%'
OR Field LIKE '%working%'

No guarantees on speed though...

Notes from the comments - this really isn't the way to do this. Full text indexing is the approach to use, but you haven't elaborated on why it is not an option.

Upvotes: 0

Related Questions