我在使用帶引數的 sql server 全文時遇到問題
Alter Procedure[dbo].[SelectFullName]
@fullname nvarchar(45)
As
Select*from [dbo][NamePersonTB]
Where CONTAINS (fullname,'"*@fullname*"')
我想用 SAME LIKE 來全名
uj5u.com熱心網友回復：
您將 @Fullname 用單引號括起來作為文字字串。如果要使用 @Fullname 的實際值作為搜索條件，則需要將變數直接傳遞給 CONTAINS 函式
另請注意，如果您想利用全文索引，則不能以%SearchTerm%完全相同的方式進行操作。您可以搜索具有匹配前綴但不匹配后綴/中間的單詞。有關通配符 (*) 與 CONTAINS 一起使用的更多資訊，請參閱MS 檔案中的 <prefix_term> 部分
下面我創建了兩種設定全文搜索的方法，一個簡單的版本，一個更高級的。不確定您的業務需求，但更高級的完全利用全文索引并具有非常簡潔的“智能”排名選項
表設定
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;
全文搜索腳本
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 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/435010.html
上一篇：逗號分隔的字串