sql-server - SQL Server ContainsTable不返回以“N”开头的单词的结果

我想查询以NEAR语法检索人。当我搜索第二个单词中包含字母N的任何文本时，结果始终为空。

我有两个人在桌子上注册了里卡多，分别是“里卡多·莫瓦”和“里卡多·诺瓦”。如果搜索“ Ricardo NEAR“ Mova *”“可以，但不能搜索'Ricardo NEAR'Nova *'

编辑

4条记录（Ricardo Nova，Ricardo Novais，Ricardo Novo，Ricardo Nunes）
查询'Ricardo NEAR'N *'
结果仅显示“ Ricardo Novais”和“ Ricardo Nunes”。

表：

CREATE TABLE [dbo].[EntitySearch](
    [IdEntity] [int] NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Accessibility] [bit] NOT NULL,
    [Document] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [Phone] [nvarchar](max) NULL,
    [Phone2] [nvarchar](max) NULL,
    [Birthdate] [datetime] NULL,
    [Gender] [int] NULL,
    [IsAct] [bit] NOT NULL,
    [Discriminator] [int] NOT NULL,
 CONSTRAINT [PK_dbo.EntitySearch] PRIMARY KEY CLUSTERED
(
    [IdEntity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =     OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE FULLTEXT CATALOG main_catalog;

完整索引

CREATE FULLTEXT INDEX ON dbo.EntitySearch
        (       [Name]
            Language[Brazilian],
                [Document]
            Language[Brazilian],
                [Email]
            Language[Brazilian],
                [Phone]
            Language[Brazilian],
                [Phone2]
            Language[Brazilian] )
KEY INDEX [PK_dbo.EntitySearch] ON main_catalog;

查询：

SELECT top 10
    FT_TBL.[IdEntity]
    ,FT_TBL.[Name]
    ,FT_TBL.[Accessibility]
    ,FT_TBL.[Document]
    ,FT_TBL.[Email]
    ,FT_TBL.[Phone]
    ,FT_TBL.[Phone2]
    ,FT_TBL.[Birthdate]
    ,FT_TBL.[Gender]
    ,FT_TBL.[IsAct]
    ,FT_TBL.[Discriminator]
    FROM [EntitySearch] AS FT_TBL INNER JOIN
    CONTAINSTABLE ([EntitySearch], [Name], 'Ricardo NEAR "Nova*"' ) AS KEY_TBL
    ON FT_TBL.[IdEntity] = KEY_TBL.[KEY]
    WHERE
        FT_TBL.[IsAct] = 1
    and FT_TBL.[Discriminator] = 2
    and KEY_TBL.RANK > 10
    ORDER BY KEY_TBL.RANK DESC, FT_TBL.[Name]

我希望显示1条记录，但没有显示。谢谢！

编辑：替代方法

我放弃了，用葡萄牙语翻译了全文索引。问题已经消失了，所以我猜“ bug”是在巴西语言上作为索引。

最佳答案

这不是错误。

每个语言都有您的自停单词列表，这意味着某些单词与索引搜索无关。

https://docs.microsoft.com/en-us/sql/relational-databases/search/configure-and-manage-stopwords-and-stoplists-for-full-text-search?view=sql-server-2017

SELECT *
FROM sys.fulltext_system_stopwords
WHERE language_id = 1046 --Brazilian language ID

因此，以我为例，我做了一种算法，可以在发送之前更正搜索查询。

关于sql-server - SQL Server ContainsTable不返回以“N”开头的单词的结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54478757/

ricardo

sql-server - SQL Server ContainsTable不返回以“N”开头的单词的结果