Help using SQL Contains statement

Chris Jones
Chris Jones used Ask the Experts™
on
Hello

i have a sql table and i would like to run a contains statement on it. i get an error saying that i can not run the script  can anyone help me with this issue

SCRIPT Below
SELECT  ID, PName, Name, DateOpen, DateNeed, CloseDate, Pri, Rname, Phone, Dept, Des, SubmitID, Status, OpenClose, ReCWID, Test, Help, ApprovedBy, DateApprove, Approved, Short_Des, WhoOpen, WhoOpenUIN
FROM    TS_WOS_WorkOrder
WHERE  CONTAINS(Des, 'Book')

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What is the error you're receiving?
Seems you forgot to put field name before contains.

SELECT  ID, PName, Name, DateOpen, DateNeed, CloseDate, Pri, Rname, Phone, Dept, Des, SubmitID, Status, OpenClose, ReCWID, Test, Help, ApprovedBy, DateApprove, Approved, Short_Des, WhoOpen, WhoOpenUIN
FROM    TS_WOS_WorkOrder
WHERE  <WHICH_FIELD_NAME> CONTAINS(Des, 'Book')
Chris JonesLead Application Web Developer

Author

Commented:
image attached
Capture.JPG
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Chris JonesLead Application Web Developer

Author

Commented:
@ lucky_james: when i add a field in front of contains i get an error

image of error below
Capture2.JPG
Top Expert 2010

Commented:
Your syntax in the original code is correct. You just need to make your table full-text indexed. Refer to the following link:
http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

Commented:
You're query is correct.

You need to fulltext index the Des column
Chris JonesLead Application Web Developer

Author

Commented:
when i run the fulltext script i get this error

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FULLTEXT'.


SCRIPT BELOW

CREATE FULLTEXT INDEX ON TCPROD.dbo.TS_WOS_WorkOrder
(
Short_Des
Language 0X0
)

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
what version/edition of SQL Server do you have?`also, you need to remove the database prefix, and change to the database context to run this statement
use TCPROD
go
CREATE FULLTEXT INDEX ON dbo.TS_WOS_WorkOrder
( Short_Des
, [Language 0X0]
)

Open in new window

Chris JonesLead Application Web Developer

Author

Commented:
i get an error with your script

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FULLTEXT'.
Top Expert 2010

Commented:
Chris JonesLead Application Web Developer

Author

Commented:
i think the error is comeing from the keyword FULLTEXT
Chris JonesLead Application Web Developer

Author

Commented:
i did some research and i found out what the issue was i am running server 2000 so the script is incorrect.

but in the sql manager i get this error when i try to convert to fulltext

The selected table has no
unique single column index on a column that does not allow NULLS.

Database:   TCPROD

Table:   [dbo].[TS_WOS_WorkOrder]
Top Expert 2010

Commented:
Have you looked at this link to set up fulltext indexing for sql 2000:
http://www.eggheadcafe.com/articles/20010422.asp
Chris JonesLead Application Web Developer

Author

Commented:
yes that was the screen the erorr showed up in
Chris JonesLead Application Web Developer

Author

Commented:
here is the screenshot
cap3.JPG
Top Expert 2010

Commented:
Guess you need to modify that in your table to have a column that is unique and does not allow nulls (Primary Key).
Do you have a primary key column in your table?
Chris JonesLead Application Web Developer

Author

Commented:
yes i have a PK in my table
Chris JonesLead Application Web Developer

Author

Commented:
here is my table structure
USE [TCPROD]
GO
/****** Object:  Table [dbo].[TS_WOS_WorkOrder]    Script Date: 10/19/2010 15:07:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TS_WOS_WorkOrder](
	[ID] [int] NOT NULL,
	[PName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Name] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DateOpen] [datetime] NOT NULL CONSTRAINT [DF_TS_WOS_WorkOrder_DateOpen]  DEFAULT (getdate()),
	[DateNeed] [datetime] NULL,
	[CloseDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pri] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Rname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Dept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Des] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SubmitID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OpenClose] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ReCWID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Test] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Help] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ApprovedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DateApprove] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Approved] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Short_Des] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[WhoOpen] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[WhoOpenUIN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UnID] [int] IDENTITY(1,10) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
that table has an ID value, value no column marked as primary key
USE [TCPROD]
GO
/****** Object:  Table [dbo].[TS_WOS_WorkOrder]    Script Date: 10/19/2010 15:07:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TS_WOS_WorkOrder](
        [ID] [int] NOT NULL,
        [PName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Name] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DateOpen] [datetime] NOT NULL CONSTRAINT [DF_TS_WOS_WorkOrder_DateOpen]  DEFAULT (getdate()),
        [DateNeed] [datetime] NULL,
        [CloseDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Pri] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Rname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Dept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Des] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SubmitID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [OpenClose] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ReCWID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Test] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Help] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ApprovedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DateApprove] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Approved] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Short_Des] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WhoOpen] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WhoOpenUIN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [UnID] [int] IDENTITY(1,10) NOT NULL PRIMARY KEY
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial