full text search question

Question

Post reply

full text search question

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
March 9, 2006 at 12:22 pm

#111881

I just set up a full text catalog on a memo field of an order table. The memo is a text field. Our company CSRs often search in the field giving the ship tracking number,so e.g. for this number: 1Z01857W0343456296,
if I run
select ommemo from tblordermemo where contains(OmMemo, '"1Z01*"')
that will return the result.
But how do I get the same result if I only know the number from the middle?
select ommemo from tblordermemo where contains(OmMemo, '"*W034*"'), of course this does not work.
Thanks in advance for any help.

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

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

jordanac Hall of Fame Points: 3603 More actions · Answer 1

Hello

where contains(OmMemo,' "W034" ')

That should work

Andy.

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 2

Thanks, Andy. I tried it, didnt work tho. I read about the string that u can put in the double quote, its called 'prefix term', meaning it returns results with words starting with that string. But sometimes, in our case, CSRs only know the middle part of a tracking number, how do we get around with full text search?

Steve Fine Ten Centuries Points: 1134 More actions · Answer 3

Couldn't it be a simple Like statement??

select ommemo from tblordermemo where OmMemo like '%W034%'

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 4

Thanks, Steve. Yes, could be. In fact, thats how we are doing now. But since memo field is a text field, its very slow to use 'like'. I am trying to make the search faster using full text search, its much faster. The only thing I find that full text search cannot do compared with 'like' is the case in my post. Any other thoughts?

Chris-232075 SSCrazy Points: 2130 More actions · Answer 5

Somebody can correct me if I'm wrong, but I'm afraid that FTS might not be a good approach for your tracking numbers. First, FTS is meant to be a word search and it's indexing of "words" with numbers and/or characters (ex: hyphen) can be either quirky, inconsistent or non-existent. Second, complete words are indexed, so I don't believe that FTS does not return results for partial word searches ("prefix terms" being the exception).

If users must be able to search using the middle part of a word, then I'm afraid you may have to abandon FTS and revert to the traditionional LIKE statement with wildcards (ex: WHERE tblordermemo LIKE '%W034%'). The disadvantage of this approach is that you lose the performance associated with a full-text index. However, the work-around is that you can build and maintain your own index on that field (You might consider changing the data type from a Text field to a VarChar field).

Good luck.

Steve Fine Ten Centuries Points: 1134 More actions · Answer 6

Nope sorry. In our databases the ShipNo is a seperate and indexed field. Makes it pretty quick to locate what one needs.

Chris-232075 SSCrazy Points: 2130 More actions · Answer 7

Sorry, I mixed the table name with the field name in my example. I should have written WHERE ommemo LIKE '%W034%'.

After submitting my post, I read that you're using the LIKE clause. Like I said, FTS might not be viable. If not, the "ommemo" field sounds like a great candidate for a VarChar field with a clustered index built on it.

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 8

Thanks all for the thoughts. Here is our scenario: --the memo field could potentially be very lengthy, with the limitation on the varchar field (8000 char), its not quite feasible to use varchar for the data type. --we have a tracking number field, which is indexed. The thing is, the field does not get filled all the time, instead its got put in the memo field, the CSRs know that, so they sometimes search in the memo field. This is just that one case that I could think of that full text index cannot accomodate compared with 'like', other than that, most time they just search on fully spelled word, such as customer first name, last name, city, state, product name, etc., which FTS works great for.