how do i do a partial string search in a Full Text Index?

Question

how do i do a partial string search in a Full Text Index?

stiej1977

Ten Centuries

Points: 1132
More actions
November 22, 2018 at 4:30 am

#368964

Hi
I've set up a full text catalog and index on a column in my table.
I need to mimic the exact result that using a LIKE '%mypartialstring%' would return.
I'm having no luck finding partial strings that i know exist in the column and the index has been repopulated.
I've tried prefixing and suffixing an asterisk onto the partial string, but it still seems to operate on whole words anyway.
any help much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Are you not looking for CONTAINS (Transact-SQL)? Even so, if your LIKE isn't returning a result, that means that no rows contain the string "mypartialstring".

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

stiej1977 Ten Centuries Points: 1132 More actions · Answer 2

yes i've been using contains. The LIKE does return the row. Contains doesn't.

Thom A SSC Guru Points: 99133 More actions · Answer 3

stiej1977 - Thursday, November 22, 2018 5:32 AM
yes i've been using contains. The LIKE does return the row. Contains doesn't.

The post you made suggests you are using LIKE; you make no mention of CONTAINS. Post the code you are using (both the LIKE and CONTAINS).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

stiej1977 Ten Centuries Points: 1132 More actions · Answer 4

ok so LIKE:

select * from table where description LIKE '%ear%'
would return row with "ear", "ears", "bears", "years", "earth", "my ear hurts", "my ears hurt", etc

now if description has a full text index on it then
select * from table where contains(description, "ear")

only pull back rows with the complete word "ear" in it, i.e "my ear hurts", but won't pull back "my ears hurt".

select * from table where contains(description, "ear*") returns both "my ear hurts" and "my ears hurt". but wouldn't return all the different what LIKE returns (earth/bears/years/etc).

i just want it to return the same results as LIKE. i'm going for the FTI for the speed. and the the like '%something%' forces a table scan and the table is big.