'CONTAINS cannot be used in a JOIN clause when using a FREE TEXT Index?

I am looking at tables like this (I did not design them):

--drop table person
CREATE TABLE EventIDsToSearch (id int not null)
insert into EventIDsToSearch  values (1)
insert into EventIDsToSearch  values (92)
insert into EventIDsToSearch  values (106)
--etc
CREATE TABLE Person (ID INT IDENTITY NOT NULL,  [type] int, Notes nvarchar(1000), CONSTRAINT [PersonPK] PRIMARY KEY CLUSTERED (ID,[TYPE]))

insert into Person ([type], notes) values (1, 'This person is linked to Event ID 92')
insert into Person ([type], notes) values (2, 'Look at ID 67!') 
insert into Person ([type], notes) values (3, 'ID 87(3/10/15)')
insert into Person ([type], notes) values (4, '!!!187(this is the event id)')--Notice the exclamation marks at the start - I don't believe WHERE CONTAINS (Notes,67) would find this.
--etc

I have tried this:

CREATE UNIQUE INDEX PersonIndex ON Person(ID);  
    CREATE FULLTEXT CATALOG ft AS DEFAULT;  
    CREATE FULLTEXT INDEX ON Person(Notes)   
       KEY INDEX PersonIndex   
       WITH STOPLIST = SYSTEM; 

and I can find all the IDs except 187. For example this finds one row:

SELECT * 
FROM Person
WHERE CONTAINS(Notes,'92') ;

but this does not:

SELECT * 
FROM Person
WHERE CONTAINS(Notes,'187') ;

I don't think a free text index is suitable for me here because:

  1. Entity.Notes sometimes contains characters e.g. exclamation marks before the ID.
  2. EventIDsToSearch contains millions of rows and it would be too slow to loop through them in a cursor. This question tells me that a cursor is the only option i.e. EventIDsToSearch and Person cannot be joined by a CONTAINS clause: Using JOIN statement with CONTAINS function

I am using SQL Server 2019.

Have I understood this correctly?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source