SQL Fulltext NEAR query

Question

Post reply

SQL Fulltext NEAR query

SteveC71

SSC Enthusiast

Points: 114
More actions
June 8, 2021 at 11:37 am

#3893357

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!

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

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
);

SteveC71 SSC Enthusiast Points: 114 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

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