I think I'm missing something simple here...
For example, given the word absolutely, return rows
absolute
absol
ab
absolutely
I tried CONTAINS
SELECT word
FROM [dbo].[SentimentModel1]
WHERE contains(search,'absolutely')
-
SELECT word FROM [dbo].[SentimentModel1] WHERE columnN LIKE 'ab%'... What about something like this?DxTx– DxTx2018-04-21 03:39:31 +00:00Commented Apr 21, 2018 at 3:39
Add a comment
|
1 Answer
What you are looking for is:
SELECT word FROM [dbo].[SentimentModel1] WHERE word LIKE '%absol%'
You should use the smaller part you want. That should generalize in some form. A better way to do it is to use soundex:
SELECT word FROM [dbo].[SentimentModel1] WHERE SOUNDEX(word) = SOUNDEX('absol')
OR
SELECT word FROM [dbo].[SentimentModel1] WHERE SOUNDEX(word) = SOUNDEX('absolutely')
Another way you can do it is to split the word into characters: T-SQL Split Word into characters After that all you need is to search the combination of characters.
the index position 0,
index position 0+1,
0+1+2,
etc...
For better performance you could add all the searches to a temporary table and index it. Full text search could be also helpful...
5 Comments
PNC
Hi @Kim - Thanks- unfortunately I can't abbreviate the word it needs to be 'absolutely' I also can't have soundex as the match must be exactly the same as the start to the word...
Kim Lage
Another way you can do it is to split the word into characters: stackoverflow.com/questions/8517816/… After that all you need is to search the combination of characters. the index position 0, index position 0+1, 0+1+2, etc...
PNC
That would work Kim - I have lots of words to search, so the permutations are going to be large. Was hoping for a more out-of-the-box approach.
Kim Lage
Well, whats is the volume are we talking about? could you calculate all the permutations into a temporary table and index it? Full text search could help with that too...please let me know if that was helpful so I can update the answer...
PNC
Hi Kim, yes permutations are huge and will be called many thousands of times - will need to have a good think about my options here. Yes your suggestion is helpful - thanks.