Different results using CONTAINS and LIKE

Question

Different results using CONTAINS and LIKE

sudhanva

Ten Centuries

Points: 1343
More actions
August 10, 2009 at 6:00 am

#135191

Hi,
create table Testtable
(
ID INT NOT NULL IDENTITY,
ColumnName varchar(max),
CONSTRAINT [PK_ColumnName_ID_Temp] PRIMARY KEY CLUSTERED (ID ASC)
)
-- Create a full-text catalog and set it as the default.
CREATE FULLTEXT CATALOG ftcTemp
AS DEFAULT
GO
-- Create a full-text index on the table.
CREATE FULLTEXT INDEX ON Testtable(ColumnName)
KEY INDEX PK_ColumnName_ID_Temp
ON ftcTemp
insert into Testtable (ColumnName) values('Speed.');
insert into Testtable (ColumnName) values('jhg j jg jgkjhg jh vbv n Speed fsdfjh jbhjf Speed.');
insert into Testtable (ColumnName) values('Fuel Efficient');
insert into Testtable (ColumnName) values('dfnbmnb bmb ,mbdfdfhl kh kh Sports Utility dfsd fsdf');
insert into Testtable (ColumnName) values('Sports MultiUtility');
insert into Testtable (ColumnName) values('dfgdf dfsf bnmbbtrerb mbmb');
insert into Testtable (ColumnName) values('dfgdf dfsf bnmbbtrerb mbmb gdf dsfd fsdf df Speed gfdg fg dfg fdg Fuel Efficient gfg ffgf');
select * from Testtable where ColumnName like '%Utility%'
select * from Testtable where contains([ColumnName], 'Utility')
I have few questions:
1) How to repopulate full-text catalogs using sql query.? Is it neccessary after every insert?
2) Why am I getting different results in the above query? OR How can i write a query using CONTAINS, which should return same set of results like '%Utility%'
Thanks & Regards,
Sudhanva

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I don’t think that you can use contains to get the same information that you got on your other query that used the like operator. The main problem is that when you use the like operator the way you did (like ‘%Utility%’), the server doesn’t look for the word utility. Instead it looks for combination of the letters between the percent symbols. This means that if the column has the value “I think that MyUtilityIsGrate and I love it” it will find it. Contains on the other hand is looking for a word. Since the word Utility is surrounded by other words without space, punctuation marks or other symbols that let the fulltext know where the word begins or ends, contains operator will not be able to notice the word Utility in this phrase.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I agree with Adi. I would also like to ask why you are insisting on a solution that uses CONTAINS. You know that you have a solution using LIKE.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sudhanva Ten Centuries Points: 1343 More actions · Answer 3

Hi,

Thanks for the reply.

Bob Hovious (8/11/2009)
I would also like to ask why you are insisting on a solution that uses CONTAINS. You know that you have a solution using LIKE.

For this please refer this article

I hope you will know, why i'm insisting on CONTAINS or FULL TEXT INDEX.

Also please answer my two question?

Thanks & Regards,

Sudhanva

Mauve SSChampion Points: 11316 More actions · Answer 4

1) How to repopulate full-text catalogs using sql query.? Is it neccessary after every insert?

Yes and No. It all depends how you set up your full-text index. Reference CHANGE_TRACKING in the documentation in the Books Online (BOL): http://technet.microsoft.com/en-us/library/ms187317(SQL.90).aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Mauve SSChampion Points: 11316 More actions · Answer 5

Some links for understanding and reference:

Hilary Cotter's article: http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

Full-Text Search Q and A http://msdn.microsoft.com/en-us/sqlserver/aa336328.aspx which contains the answer to the Question: why do we need full text if we can use "like" in select query?

Microsoft Technical Article SQL Server 2005 Full-Text Search: Internals and Enhancements

http://msdn.microsoft.com/en-us/library/ms345119(SQL.90).aspx which contains an answer (http://msdn.microsoft.com/en-us/library/ms345119(SQL.90).aspx#yukonftsea_topic2) to the question:

What Can Full-Text Search Do for Me?

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Different results using CONTAINS and LIKE

Cookies on SQLServerCentral