2

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.

1
  • I believe it is related to SQL Server 2019. I'm runnnig a Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) Nov 1 2020 00:13:28 Copyright (c) Microsoft Corporation Developer Edition (64-bit) and both queries returned a result.
    – Ronaldo
    Feb 24, 2022 at 11:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.