SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issue with Full Text Search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Access
Starting Member

44 Posts

Posted - 08/18/2014 :  17:28:26  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 08/19/2014 :  04:52:53  Show Profile  Reply with Quote
SELECT *
FROM [tblProduct]
WHERE [ProductDesc] LIKE '%electro dev%'

We are the creators of our own reality!
Go to Top of Page

Access
Starting Member

44 Posts

Posted - 08/19/2014 :  10:24:34  Show Profile  Reply with Quote
quote:
Originally posted by sz1

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

We are the creators of our own reality!



Seriously?
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 08/19/2014 :  11:09:10  Show Profile  Reply with Quote
'%%' 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!

Edited by - sz1 on 08/19/2014 11:12:02
Go to Top of Page

Access
Starting Member

44 Posts

Posted - 08/19/2014 :  12:32:13  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 08/20/2014 :  04:01:44  Show Profile  Reply with Quote
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!
Go to Top of Page

Access
Starting Member

44 Posts

Posted - 08/20/2014 :  10:01:17  Show Profile  Reply with Quote
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.

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 08/20/2014 :  10:29:08  Show Profile  Reply with Quote
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!
Go to Top of Page

Access
Starting Member

44 Posts

Posted - 08/20/2014 :  13:38:37  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 08/21/2014 :  09:23:16  Show Profile  Reply with Quote
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!
Go to Top of Page

Access
Starting Member

44 Posts

Posted - 08/21/2014 :  11:53:03  Show Profile  Reply with Quote
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”.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000