full text search sql server with parameter

Question

I have a problem using the sql server full text with parameter

Alter Procedure[dbo].[SelectFullName]
@fullname nvarchar(45)
As
Select*from [dbo][NamePersonTB]
Where CONTAINS (fullname,'"*@fullname*"')

I want to use SAME LIKE to fullname

Answer 1

You are using the @Fullname as a literal string by wrapping it in single quotes. You need to pass a variable directly into the CONTAINS function if you want to use the actual value of @Fullname as your search criteria

Also note you cannot do the %SearchTerm% exactly the same way if you want to leverage your fulltext index. You can search for words that have a matching prefix, but not matching suffix/middle. For more info on wildcard(*) usage with CONTAINS, see <prefix_term> section in the MS doc

Below I've created two ways I might set up the fulltext search, a simple version and a more advanced. Not sure your business needs, but the more advanced fully leverages the fulltext index and has a "smart" ranking option that is pretty neat

Table Setup

CREATE TABLE NamePersonTB (ID INT IDENTITY(1,1) CONSTRAINT PK_NamePersonTB Primary Key,FullName NVARCHAR(100))
INSERT INTO NamePersonTB
VALUES ('John Smith')
    ,('Jane Smith')
    ,('Bill Gates')
    ,('Satya Nadella')

CREATE FULLTEXT CATALOG ct_test AS DEFAULT;
CREATE FULLTEXT INDEX ON NamePersonTB(FullName) KEY INDEX PK_NamePersonTB;

Fulltext Search Script

DECLARE @FullName NVARCHAR(45);

/*Sample searches*/
SET @FullName = 'John Smith' /*Notice John Smith appears first in ranked search*/
--SET @FullName = 'Smith'
--SET @FullName = 'Sm'
--SET @FullName = 'Bill'

DECLARE @SimpleContainsSearchCriteria NVARCHAR(1000)
    ,@RankedContainsSearchCriteria NVARCHAR(1000)

/*
Below will
    1. Parses the words into rows
    2. Adds wildcard to end(cannot add wildcard to prefix according to MS doc on CONTAINS)
    3. Combines all words back into single row with separator to create CONTAINS search criteria
*/
SELECT @SimpleContainsSearchCriteria  = STRING_AGG(CONCAT('"',A.[Value],'*"'),' AND ')
FROM STRING_SPLIT(REPLACE(@Fullname,'"',''),' ') AS A /*REPLACE() removes any double quotes as they will break your search*/

/*Same as above, but uses OR to include more results and will utilize [Rank] so better matches appear first*/
SELECT @RankedContainsSearchCriteria = STRING_AGG(CONCAT('"',A.[Value],'*"'),' OR ')
FROM STRING_SPLIT(REPLACE(@Fullname,'"',''),' ') AS A 

/*Included so you can see the search critieria. Should remove in final proc*/
SELECT @Fullname AS FullNameInput
    ,@SimpleContainsSearchCriteria  AS SimpleSearchCriteria
    ,@RankedContainsSearchCriteria AS RankedContainsSearchCriteria

/*Simple AND match*/
SELECT *
FROM NamePersonTB AS A 
WHERE CONTAINS(FullName,@SimpleContainsSearchCriteria)

/*CONTAINSTABLE match alternative. Uses OR criteria and then ranks so best matches appear at the top*/
SELECT *
FROM CONTAINSTABLE(NamePersonTB,FullName,@RankedContainsSearchCriteria) AS A
INNER JOIN NamePersonTB AS B
    ON A.[Key] = B.ID
ORDER BY A.[Rank] DESC

Sample Search Criteria

FullNameInput	SimpleSearchCriteria	RankedContainsSearchCriteria
John Smith	"John*" AND "Smith*"	"John*" OR "Smith*"

Output of Simple Search

ID	FullName
1	John Smith

Output of Ranked Search

KEY	RANK	ID	FullName
1	48	1	John Smith
2	32	2	Jane Smith