Issue with Full Text Search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with Full Text Search

Author

Topic

Access
Starting Member

44 Posts

Posted - 2014-08-18 : 17:28:26

I have a table that has a row with below value in the indexed field (mapped to a catalog):

"Portable battery charger with charging adapters for all major electronic devices"

When I run the following query, I got this record in the result set. Any idea why does this row is returned in this query? According to MSDN, when add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. In my case the term does not match, it should be “electronic dev” to match….I’m not following

SELECT *
FROM tblProduct
WHERE (CONTAINS (ProductDesc, '"electro dev*"'))

Thank you

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 04:52:53

SELECT *
FROM [tblProduct]
WHERE [ProductDesc] LIKE '%electro dev%'

We are the creators of our own reality!

Access
Starting Member

44 Posts

Posted - 2014-08-19 : 10:24:34

quote:
Originally posted by sz1

SELECT *
FROM [tblProduct]
WHERE [ProductDesc] LIKE '%electro dev%'

We are the creators of our own reality!

Seriously?

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 11:09:10

'%%' everything between the percent signs will give you a match, for an exact match you can use 'electro dev' to only return that exact match.

If you are looking for electronic dev, why are you not using 'electronic dev'

for more info on contains:
http://msdn.microsoft.com/en-us/library/ms187787.aspx

We are the creators of our own reality!

Access
Starting Member

44 Posts

Posted - 2014-08-19 : 12:32:13

quote:
Originally posted by sz1

'%%' everything between the percent signs will give you a match, for an exact match you can use 'electro dev' to only return that exact match.

If you are looking for electronic dev, why are you not using 'electronic dev'

for more info on contains:
http://msdn.microsoft.com/en-us/library/ms187787.aspx

We are the creators of our own reality!

Exactly my point.

The ProductDesc LIKE 'electro dev%' does not retrieve the record, while (CONTAINS (ProductDesc, '"electro dev*"')) does.

In my mind the FTS way should not be a match, because of the white space "divider".

Thanks.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-20 : 04:01:44

So you must have full text enabled to use the contain. Have you tried Like 'electronic dev'

We are the creators of our own reality!

Access
Starting Member

44 Posts

Posted - 2014-08-20 : 10:01:17

quote:
Originally posted by sz1

So you must have full text enabled to use the contain. Have you tried Like 'electronic dev'

We are the creators of our own reality!

The full text is enabled as i mentioned in my original post.
Why would I try "LIKE 'electronic dev'"? Of course it will not return the record.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-20 : 10:29:08

Its a very simple task this, so not sure whats happening but you can get the exact match string search by using

select *
From tblProduct
Where ProductDesc Like 'electronic dev'

single quotes not double! that is if you want to ignore other matches other than that specified inside the single quotes.

We are the creators of our own reality!

Access
Starting Member

44 Posts

Posted - 2014-08-20 : 13:38:37

quote:
Originally posted by sz1

Its a very simple task this, so not sure whats happening but you can get the exact match string search by using

select *
From tblProduct
Where ProductDesc Like 'electronic dev'

single quotes not double! that is if you want to ignore other matches other than that specified inside the single quotes.

We are the creators of our own reality!

Have no intention using LIKE, need to understand why CONTAINS doesn't work the way it should.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-21 : 09:23:16

we are here to help where we can, that means using our minds to create sometimes different approaches to certain scenarios, if you dont use like which will work then you are not understanding the purpose of this forum. Research more on CONTAINS if you need that function but the answer you seek is not built in stone.

We are the creators of our own reality!

Access
Starting Member

44 Posts

Posted - 2014-08-21 : 11:53:03

quote:
Originally posted by sz1

we are here to help where we can, that means using our minds to create sometimes different approaches to certain scenarios, if you dont use like which will work then you are not understanding the purpose of this forum. Research more on CONTAINS if you need that function but the answer you seek is not built in stone.

We are the creators of our own reality!

The overall reason for this forum is to help others and it seems like you are trying to do that, but without addressing the main issue.

Your help is based on “forget about your problem, do it this way.”
I asked a straight forward question, but it looks like you hijacked it with your “other approaches”.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources