如何在SQL Server中包含字符串的每个单词的搜索查询? [英] How to make a search query where contain each words of string in SQL Server?

问题描述

我想做一个搜索 sql 查询来查找包含用户输入的字符串的名称.它类似于下面的查询，但我不知道如何制作where"部分.我一直在谷歌上找，但我还是找不到合适的.

I want to make a search sql query to find a name where contain the string that users input. It is something like the query below, but I don't know how to make the 'where' part. I have been looking in google but I still can't find the right one.

 DECLARE @string varchar(20) 
 SELECT @string = 'test complete name'
 SELECT complete_name from users 
    where complete_name like '%test%'
    or complete_name like '%complete%'
    or complete_name like '%name%'
    or complete_name like '%test complete%'
    or complete_name like '%test name%'
    or complete_name like '%complete name%'
    or complete_name like '%test complete name%'

推荐答案

创建一个如下所示的函数，用于拆分给定的字符串并从给定的输入返回单个单词

Create function fn_stringSplit(@StringSplit varchar(max))
returns @table table(SplitValue varchar(10) not null)
as
begin
    Declare @StartVal int
    Declare @endVal int
    set @StringSplit = @StringSplit + ' '
    set @StartVal = 1
    set @endVal = 1
    while @endVal >= 0
        begin
            set  @endVal = charindex(' ',@StringSplit,@StartVal);
            insert into @table select SUBSTRING(@StringSplit,@StartVal,@endVal-1)
            set @StringSplit= SUBSTRING(@StringSplit,@endVal+1, LEN(@StringSplit))
            if @StringSplit = '' set @endVal= -1
        end
    return
end

现在通过传递输入在主查询中调用我们的函数

DECLARE @string varchar(20) 
SELECT @string = 'Marketing tool designer'
SELECT JobTitle from Employee e
   where JobTitle in (select e.JobTitle from fn_stringSplit(@string) ss
                            where e.JobTitle like '%'+SplitValue+'%')  --Here Splitvalue is the column name in the table that is returned by fn_stringSplitfunction

在 SQL SERVER 2016 中，我们有一个 String_Split 函数，我们可以将查询写为

in SQL SERVER 2016 we have a function String_Split we can write query as

DECLARE @string varchar(20) 
SELECT @string = 'Marketing tool designer'
SELECT JobTitle from Employee e
   where JobTitle in (select e.JobTitle from String_Split(@string) ss
                            where e.JobTitle like '%'+Value+'%') --Here **value** is the column name in the table that is returned by **String_Split**

这篇关于如何在SQL Server中包含字符串的每个单词的搜索查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！