My ContainsTable query is not working - please help :)

Question

Hi folks,

I've got a full text catalogue setup. It has a unique key count of 117 with 19 items. The table has 19 rows only.

The table has an NVARCHAR(50) field called ClientGuid. It's a guid with some weird text at the end.

eg..

1. 8b6ef4a504dd1a57f079180e7f6eb4a0(-)
2. 8b6ef4a504dd1a57f079180e7f6eb4a0(OK)

(and no, i didn't defined that text field data - we're sourcing it from a 3rd party API.

anways, this is my sql and the query i run against it. When i run the query, i get ZERO results back :(

ALTER FUNCTION [dbo].[Foo_HiJonSkeet]
(   
    @ClientGuid NVARCHAR(50)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT KEY_TBL.[Key] as LogEntryId,
        KEY_TBL.RANK as Relevance
    FROM CONTAINSTABLE(LogEntries, ClientGuid, @ClientGuid) AS KEY_TBL
)


SELECT * FROM  Foo_HiJonSkeet('8b')

Any suggestions? Server is Sql Server 2008.

Answer 1

You can try the following construction:

SELECT * FROM  Foo_HiJonSkeet('"8b*"')

adding the double quotes and an asterisk after the original search term. It should work.

But in the case if all the searches will be similar to the example you've posted above, I advise you to use LIKE statement instead of using full text search.