0

My table is as follows:

CREATE TABLE [dbo].[Hers] (
    [Material] [int] NOT NULL,
    [Material_Description] [nvarchar](255) NULL,
    [Material_Type] [nvarchar](255) NULL,
    [Manufacturer] [nvarchar](255) NULL,
    [MPN] [nvarchar](max) NULL,
    [X_plant_matl_status] [nvarchar](255) NULL,
    [Old_material_number] [nvarchar](255) NULL,
    [Int_material_number] [int] NULL,
    CONSTRAINT [PK_Hers] PRIMARY KEY CLUSTERED 
    (
        [Material] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I need to use this kind of select:

SELECT *
FROM [dbo].[Hers]
WHERE FREETEXT(MPN, 'dx64')

but I have tried to create a FULLTEXT INDEX on the MPN column as follows:

CREATE FULLTEXT INDEX ON [dbo].[Hers](MPN) KEY INDEX PK_Hers WITH STOPLIST = SYSTEM;

But I am getting an error:

TYPE COLUMN option is not allowed for column types other than image or varbinary(max).

How can I create a FULLTEXT index on the MPN column?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ashan Dulanja Udeshitha
  • 43
  • 8
  • What was the statement you used to try to create the fulltext index? – Thom A Apr 29 '20 at 08:07
  • CREATE FULLTEXT INDEX ON [dbo].[Hers](MPN) KEY INDEX PK_Hers WITH STOPLIST = SYSTEM; – Ashan Dulanja Udeshitha Apr 29 '20 at 08:14
  • Are you 100% sure thats the SQL you are using? According to the [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-ver15) that error should only occur if you use `TYPE COLUMN type_column_name`. – Dale K May 20 '20 at 01:54

0 Answers0