full-text indexing with partical word

Question

full-text indexing with partical word

lleemon13

Ten Centuries

Points: 1063
More actions
April 10, 2011 at 8:46 pm

#234687

My table is 'product' and the field searching is 'product_name'.
if my product_name values are:
articles
sunny valley
monster truck
wii sports
black eight ball
what's the equivalent to:
select * from product where (product_name like '%nste%') OR (product_name like '%ny%')
which returns two records:
sunny valley
monster truck
if I do the following it doesn't return the same results.
select * from product where contains(PRODUCT_NAME, '"*nste*" OR "*ny*"')
does this sound right?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

lleemon13 (4/10/2011)
My table is 'product' and the field searching is 'product_name'.
if my product_name values are:
articles
sunny valley
monster truck
wii sports
black eight ball
what's the equivalent to:
select * from product where (product_name like '%nste%') OR (product_name like '%ny%')
which returns two records:
sunny valley
monster truck
if I do the following it doesn't return the same results.
select * from product where contains(PRODUCT_NAME, '"*nste*" OR "*ny*"')
does this sound right?

You've already found out that "*" isn't a wild-card in SQL Server. So I have to ask, does what sound right?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lleemon13 Ten Centuries Points: 1063 More actions · Answer 2

I am just trying to do the equivalent to the LIKE as I have above but using the full-text index.

I am trying to do a search on my site but the full-text index only appears to search full words not partial.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Sorry. I only looked at the query and not the question. My mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

I checked in Books Online and found Prefix-Terms where you can use a trailing asterick but no place where you use a leading asterick.

I'm not 100% sure but I don't believe that what you ask can be done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 5

jeff is right. full text indexing only allow trailing wildcards.

lleemon13 Ten Centuries Points: 1063 More actions · Answer 6

so what's the standard for creating a searchable site? Do you just hope the user enters a full word? Or do you keep doing the like with wildcard?

Anyone seen example of what others are doing?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Well even for google, it took them years and god knows how much money to put that in place (and just for the suggestion tools might I add).

FTS says it all.... FULL TEXT search. If you need a '%whatever' then I suggest you use like. And not it's won't be fast.

Plan Z might be to store a calculated column that holds the reverse() of the actual column. Then the partial search might be implemented in fts. I've never done it but the theor sounds good.