0

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')
kkm inactive - support strike
  • 5,190
  • 2
  • 32
  • 59
PNC
  • 1,932
  • 19
  • 36
  • SELECT word FROM [dbo].[SentimentModel1] WHERE columnN LIKE 'ab%'... What about something like this? – DxTx Apr 21 '18 at 03:39

1 Answers1

1

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...

Kim Lage
  • 117
  • 9
  • 1
    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... – PNC Apr 20 '18 at 23:07
  • 1
    Another way you can do it is to split the word into characters: https://stackoverflow.com/questions/8517816/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... – Kim Lage Apr 20 '18 at 23:17
  • 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. – PNC Apr 20 '18 at 23:24
  • 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... – Kim Lage Apr 20 '18 at 23:35
  • 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. – PNC Apr 20 '18 at 23:40