SQL Fulltext NEAR query

  • Hi,

    I'm tasked with converting some search terms from a 3rd party system to SQL Fulltext and am wondering if it's possible for this term:

    "WordA" NOT W/2 "WordB"

    So any rows where WordA appears unless it's within two words of WordB

    Any help appreciated - cheers!

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I don't think that CONTAINSTABLE allows a "NOT NEAR" to be used. At least, the docs on NEAR don't seem to show this.

    https://docs.microsoft.com/en-us/sql/relational-databases/search/search-for-words-close-to-another-word-with-near?view=sql-server-ver15

    I think a subquery that finds rows with a NEAR match, and uses those to exclude others, might be the way. As an example:

    --truncate TABLE FTSTest 
    CREATE TABLE FTSTest (
    myid INT NOT NULL IDENTITY(1,1) CONSTRAINT FTSTestPK PRIMARY KEY
    , mydata VARCHAR(MAX)
    )
    GO
    INSERT dbo.FTSTest (mydata)
    VALUES ('Now is the time for all good men to come to the aid of their country'),
    ('there are a number of men who are good in the world'),
    ('good for men that help others'),
    ('If there are men who others might consider good, we should support them'),
    ('Good is a concept that is sometimes hard for men to comprehend'),
    ('Good is a concept that is sometimes hard for anyone to comprehend')
    GO
    -- Returns 5 rows
    -- All rows have "good" and "men" in them.
    SELECT ft.*
    FROM
    dbo.FTSTest AS ft
    INNER JOIN CONTAINSTABLE
    (dbo.FTSTest, mydata, 'good and men') AS ct
    ON ft.myid = ct.
    go

    -- returns 3 rows.
    -- From above, the 1st, 2nd, and 3rd values have "good" and "men" separated by at most 2 terms.
    SELECT
    FROM CONTAINSTABLE
    (dbo.FTSTest, mydata, 'NEAR((good,men),2)') AS ct1
    GO
    -- returns 3 rows.
    -- 1st and 3rd rows eliminated since they are within 1 term.
    SELECT ft.*
    FROM
    dbo.FTSTest AS ft
    INNER JOIN CONTAINSTABLE
    (dbo.FTSTest, mydata, 'good and men') AS ct
    ON ft.myid = ct.
    WHERE ct. NOT IN (
    SELECT
    FROM CONTAINSTABLE
    (dbo.FTSTest, mydata, 'NEAR((good,men),1)') AS ct1
    );

     

  • Steve - thanks very much for the reply and the example; way more than I could have hoped for. I'll try this today.

    Agree though, I cannot see any mention of this being possible in BOL or any number of other explanations and tutorials, then again I cannot see any mention of "a NEAR b NEAR c" syntax in BOL, which is allowed. A more exhaustive treatment of this area of Fulltext is required I feel.

    Thanks again,

    Steve

  • I agree. While I was looking around, I was thinking about putting in another example there.

Viewing 5 posts - 1 through 5 (of 5 total)

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