What is the Full-Text Search Equivalent of LIKE '%'?

Question

I am using the FTS option of sql server 2008. In my queries, I would like to pass in a wildcard search.

In std sql, I have this:

SELECT * 
  FROM Person 
WHERE Lastname like '%';  -- this returns all persons

Using FTS, I would like to do something like this:

SELECT * 
  FROM Person 
 WHERE contains(*, '"*"')  -- this return no rows.

The reason I have contains is because i am passing a parameter to an sp.

For example:

exec spMySearch('formsof (inflectional, stuff)')

In spMySearch(), I have this:

select * from Person where contains(*, @SearchBy)  -- this is the param of spMySearch

Is there an approach you can recommend to getting wildcard searches? I know there is a performance hit, but it is something I would like to try and get working.

Answer 1

contains('"*"') will return zero records by design.

Is there some reason you need to do that though? Why not just do the query without the parameter if it's empty?

IF @SearchBy = '' BEGIN
    SELECT  *
        FROM    Person
END
ELSE BEGIN
    SELECT  *
        FROM    Person
        WHERE   CONTAINS(*, @SearchBy)
END