I am looking at tables like this (I did not design them):
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
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 believe this is because there is an exclamation mark at the beginning of 187. How can I also find 187? I am using SQL Server 2019.
I have looked here: Using fulltext search to find a sequence of digits. I cannot use the LIKE operator because the performance is terrible.