SQL Server 2012 FTS and weird behavior with numbers

  • I stumbled on this weird behavior in SQL Server's Full text search. First run the following SQL-code in a database:

    CREATE TABLE test(

    id int IDENTITY NOT NULL,

    value VARCHAR(255),

    CONSTRAINT test_pk PRIMARY KEY (id)

    );

    CREATE FULLTEXT CATALOG test_catalog AS DEFAULT;

    CREATE FULLTEXT INDEX ON [dbo].[Test](

    [value] LANGUAGE 'Neutral')

    KEY INDEX [test_pk]ON ([test_catalog], FILEGROUP [PRIMARY])

    WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF)

    INSERT INTO

    test (value)

    VALUES

    ('Selenium_Product_C 01/2014'),

    ('Selenium_Product_A 02/2014'),

    ('Selenium_Product_A 05/2014'),

    ('Selenium_Product_C 10/2014'),

    ('Selenium_Product_C 11/2014'),

    ('Selenium_Product_C 12/2014');

    Using FTS the following query

    SELECT * FROM CONTAINSTABLE(Test, value, '"02/2014*"' );

    correctly returns one row "Selenium_Product_A 02/2014" and so does

    SELECT * FROM CONTAINSTABLE(Test, value, '"10/2014*"' );

    witch returns "Selenium_Product_C 10/2014".

    But if I make the following search

    SELECT * FROM CONTAINSTABLE(Test, value, '"01/2014*"' );

    It returns "Selenium_Product_C 01/2014", "Selenium_Product_C 10/2014", Selenium_Product_C 11/2014 and "Selenium_Product_C 12/2014" and they all have the same Rank.

    I would assume it would only return "Selenium_Product_C 01/2014" and not all those other rows.

    Why is that?

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply