2

Suppose I have the following schema and data in SQL Server 2012:

create table Exclamations
(
    ID int not null primary key identity(1,1),
    Exclamation nvarchar(150) not null
);

insert into Exclamations(Exclamation) values
    ('Ohmygosh!'),
    ('Ohmygoodness!'),
    ('It''sabird!'),
    ('It''saplane!'),
    ('Whattheheck!'),
    ('Lookoutbelow!'),
    ('Whatintheworld?'),
    ('Isheforreal?'),
    ('Takeahike!');

create fulltext index on Exclamations(Exclamation) key index ID;

And suppose I want to do full-text queries against my exclamations, but I want to be able to query for words within words using a high speed index. Is that possible? Perhaps with a custom dictionary, or custom word-breaking/boundary behavior?

For example, I want to be able to query for the word "out" and have it quickly locate row #6 ('Lookoutbelow!') without having to scan through all the rows using a LIKE-ish operator. This also implies some false positives, for example, suppose I want to query for the word "he" and find row #8 ('Isheforreal?'); the query would also return row #5 ('Whattheheck!') since it contains the same "he" substring. That's OK.

Note that I do not need the tokenizer to index every possible combination of characters. I don't mind if nonsense words like 'sapl' within row #4 are not indexed. I only need it to tokenize the words it already recognizes (and possibly some additional words that I would add, but that is only a nice-to-have.)

It is important to specify that for the purposes of this question I cannot pre-parse the exclamations to insert obvious tokenizing boundaries such as spaces, hyphens, or periods, before inserting into SQL Server. I want to know if SQL Server can somehow do that itself.

EDIT: After some more reading, it appears that what I want is generally called N-gram tokenizing. But I want it limited to tokens that are already present as, or variants from, words in the dictionary.

  • This article seems to have a good road map for implementing a DIY version, but there aren't any out of the box features for it as far as I know. – LowlyDBA Feb 10 '17 at 22:50
  • @JohnM Yeah I saw that article, but to do that much string processing in T-SQL functions seems scary to me. I am evaluating SQL Server FTS vs. Elasticsearch for this use case and I am thinking you are right that there is no built-in feature like this in SQL Server. So I think ES will be better. – Jordan Rieger Feb 11 '17 at 0:46
0

Looks like there is no such feature in SQL Server.

  • FYI: A little while after this, I came across a feature called n-gram tokenization/indexing, which was well supported in ElasticSearch and met our use case. – Jordan Rieger Aug 13 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.