Different results from like and contains

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Kotuby - 27 Dec 2007 17:22 GMT

Hi guys,

I have 2 different queries that I expected to return the same results.

Where Name like '%fish%'

and

Where contains((Name),'("*fish*")')

The first returned 181 results and the 2nd returned 178 results.

On closer inspection I determined that the Contains was not returning Names
with words that had "fish" embedded as in:
bigfishtackle.com
flyfishing
kingfisher

I thought that the *fish* would return words where fish was a suffix, prefix
or both.

What am I doing wrong?

TIA

Reply to this Message

Olaf Pietsch - 27 Dec 2007 18:07 GMT

Hi John,

> I have 2 different queries that I expected to return the same results.
>
[quoted text clipped - 11 lines]
> I thought that the *fish* would return words where fish was a suffix,
> prefix or both.

The search with "*..." (leading asterisk) is not possible with contains,
only the "...*" is possible.

BOL says only:
<prefix_term>
Specifies a match of words or phrases beginning with the specified text.
Enclose a prefix term in double quotation marks ("") and add an asterisk (*)
before the ending quotation mark, so that all text starting with the simple
term specified before the asterisk is matched.
http://msdn2.microsoft.com/en-us/library/ms187787.aspx

Many Ragards,
Olaf

Signature

Gruß Olaf
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
Blog (http://www.sqlpass.de/PASSUserBlogs/tabid/178/Default.aspx?BlogID=3)
Regionalgruppe Köln/Bonn/Düsseldorf
(http://www.sqlpass.de/Regionalgruppen/KoelnBonnDuesseldorf/tabid/81/Default.aspx)

Reply to this Message

John Kotuby - 28 Dec 2007 14:38 GMT

Olaf,
Thanks for the heads-up on that information.

> Hi John,
>
[quoted text clipped - 27 lines]
> Many Ragards,
> Olaf

Reply to this Message