1

On a SQL Server 2012 SP3 CU8 server instance, we have PDF files that have been inserted into a table called OBJECT_FILE created as follows:

CREATE TABLE [dbo].[OBJECT_FILE](
    [FILE_ID] [int] NOT NULL,
    [FILE_FIN] [int] NOT NULL,
    [OBJ_FILE_IDX_DOCTYPE] [varchar](3) NULL,
    [FILE_TYPE] [smallint] NOT NULL,
    [FILE_TXT] [varbinary](max) NULL,
    [FILE_TXT_SIZE] [int] NULL DEFAULT ((0)),
    CONSTRAINT [PK_DM_OBJECT_FILE] PRIMARY KEY CLUSTERED (
        [FILE_ID] ASC
    )
    WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON
    ) ON [INDEXFG]
) ON [INDEXFG] TEXTIMAGE_ON [BLOBFG]

We also created a FullText Index that is built on FILE_TXT column, which is a VARBINARY(MAX), created as follows:

CREATE FULLTEXT INDEX 
ON OBJECT_FILE (
    FILE_TXT TYPE COLUMN OBJ_FILE_IDX_DOCTYPE
) 
KEY INDEX PK_DM_OBJECT_FILE 
WITH STOPLIST=OFF;

In that table, we are willing to insert the contents of old documents. In these contents, we will find a unique identifier inserted in the past which follows a naming convention:

CORP-MMM-<three_digits_0_to_9>.<three_digits_0_to_9>

When I try to do a lookup using CONTAINS built-in function on a particular identifier that I know it exists, it doesn't give me back any results. I use following WHERE clause:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.245')

although a lookup with following WHERE clause provides me multiple results:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.*')

and the record in question can be found with LIKE:

WHERE FILE_TXT LIKE '%CORP-MMM-456.245%' 

My question is:

Why can't I get a specific record regarding my WHERE clause and how could I make it work ?

11
  • try to re-build your full-text index
    – Prabhat G
    Commented Jun 27, 2017 at 12:23
  • Do you have a value in FILE_TXT column with exact match CORP-MMM-456.245? Commented Jun 27, 2017 at 13:06
  • @PrabhatG - it's a brand new Full-Text Index Commented Jun 27, 2017 at 13:10
  • @SqlWorldWide - I do have a record that has a FILE_TXT containing CORP-MMM-456.245 Commented Jun 27, 2017 at 13:10
  • does this return anything ? WHERE FILETEXT LIKE 'CORP-MMM-456.245'
    – Prabhat G
    Commented Jun 27, 2017 at 13:13

2 Answers 2

1

Sorry this might not be an answer but I cannot put this in comment. Based on the comment by OP @Jefferson (as an answer to my comment) I could not reproduce the behavior. I can get a record back with where clause exactly matching the value.

I tested this with following code (copied and modified from here)

CREATE TABLE Department 
   (DepartmentID INT IDENTITY CONSTRAINT DepartmentPK PRIMARY KEY, 
    NAME NVARCHAR(50),
    File_txt NVARCHAR(50));
GO
INSERT INTO Department (NAME, File_txt) VALUES ('OP1', 'CORP-MMM-456.245');
INSERT INTO Department (NAME, File_txt) VALUES ('OP2', 'CORP-MMM-456.246');
INSERT INTO Department (NAME, File_txt) VALUES ('OP3', 'CORP-MMM-456.247');
INSERT INTO Department (NAME, File_txt) VALUES ('OP4', 'CORP-MMM-456.245.246');

Go
CREATE FULLTEXT CATALOG ComplexFTS AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON Department(File_txt LANGUAGE 1033 /* 0 = Neutral, 1033 = American English  */)  
KEY INDEX DepartmentPK WITH (STOPLIST = OFF); /* Or use (stoplist = Off) for no stoplist */
GO
WAITFOR DELAY '00:00:05';
GO
SELECT * FROM Department WHERE CONTAINS (File_txt,'"CORP-MMM-456.*"');
SELECT * FROM Department  WHERE CONTAINS(File_txt,'CORP-MMM-456.245')
GO

This is what I get as my result set.

enter image description here

Clean up code.

DROP FULLTEXT INDEX ON Department;
GO
DROP FULLTEXT CATALOG ComplexFTS;
GO
DROP TABLE Department;
GO
4
  • Thanks. I saw you set a language during the creation of your Full-Text Index. How could I check for language used for already created Full-Text Index ? Commented Jun 27, 2017 at 13:32
  • sys.fulltext_index_columns will show that. I also tested with 0=Neutral and get same result. Commented Jun 27, 2017 at 13:42
  • It may be relevant that OP is searching a PDF stored in a varbinary, rather than a text column.
    – RDFozz
    Commented Jun 27, 2017 at 15:16
  • The language_id column is 1033 too... Commented Jun 28, 2017 at 5:36
0

The solution consisted in the change of the Full-Text Index from English to neutral language.

This can be explained using following sys.dm_fts_parser built-in function of SQL Server.

select * from sys.dm_fts_parser( '" dsolkjfdskljfsd dfsd-MMM-236.127 dojfdslfkjds"', 1033, -- English NULL, 0 ) ;

leads to the deletion of the "." character in keywords:

Full-Text String parsing with Language = English

while following code keep this character available for lookups: select * from sys.dm_fts_parser( '" dsolkjfdskljfsd dfsd-MMM-236.127 dojfdslfkjds"', 0, -- Neutral NULL, 0 ) ; Results:

Full-Text String parsing with Language = Neutral

We dropped existing index and created it using LANGUAGE 0 option and this solved our problem.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.