时间戳

首页 > 解决方案 > 使 sql where 语句为 string_split 中的每个值动态

sql - 使 sql where 语句为 string_split 中的每个值动态

问题描述

我目前正在使用相对静态查询搜索表。这不是一个完美的查询，因为此示例中的原始搜索输入是Denny drive. 搜索输入应该用空格分隔（可能用string_split）。因此，我只想要行、where和dennyis drivein或.givennamesurenamestreetaddress

这是当前对 zwei therms 的查询：

DECLARE @query1 NVARCHAR(MAX)
DECLARE @query2 NVARCHAR(MAX)

SET @query1='Denny'
SET @query2='drive'

SELECT
    *
FROM
    fakenames f
WHERE
    (givenname like '%' + @query1 + '%' OR surname like '%' + @query1 + '%' OR streetaddress like '%' + @query1 + '%') AND 
    (givenname like '%' + @query2 + '%' OR surname like '%' + @query2 + '%' OR streetaddress like '%' + @query2 + '%')

是否可以使 where 语句更灵活？这样我就可以只有一个变量SET @query='Denny drive vegas'和每个拆分元素的 where 语句与另外一个组合(givenname like '%' + @queryY + '%' OR surname like '%' + @queryY + '%' OR streetaddress like '%' + @queryY + '%')

样本数据：

| givenname | surname  | streetaddress        |
|:----------|---------:|:--------------------:|
| Irene     | Williams | 2835 Crestview Manor |
| Denny     | Wade     | 885 Baker drive      |
| Denny     | Reese    | 129 Owagner Lane     |

预期结果：

| givenname | surname  | streetaddress        |
|:----------|---------:|:--------------------:|
| Denny     | Wade     | 885 Baker drive      |

标签： sqlsql-serversplitwhere-in

解决方案

这是灵活的：

Table
Create Table fakenames
(
givenname nVarChar(25),
surname nVarChar(25),
streetaddress nVarChar(50)
)
Insert Into fakenames Values
('Denny','Smith','Main St'),
('Billy','vegas','Main St'),
('John','Travolta','Main St'),
('Mick','Jagger','Willow Ave'),
('Mary','Jane','Oak drive')

询问

Declare @query nVarChar(50) = 'Denny drive vegas'

;With cte As
(
SELECT
    f.*, 
    v1.value As val1,
    v2.value As val2,
    v3.value As val3
FROM
    fakenames f Left Join
    String_Split(@query,' ') v1 On f.givenname Like '%' + v1.value + '%' Left Join
    String_Split(@query,' ') v2 On f.surname Like '%' + v2.value + '%' Left Join
    String_Split(@query,' ') v3 On f.streetaddress Like '%' + v3.value + '%'
)
Select * From cte
WHERE
    val1 Is Not Null Or
    val2 Is Not Null Or
    val3 Is Not Null

结果

givenname   surname streetaddress   val1    val2    val3
---------   ------- -------------   ---     ---     ---
Denny       Smith   Main St         Denny   NULL    NULL
Billy       vegas   Main St         NULL    vegas   NULL
Mary        Jane    Oak drive       NULL    NULL    drive

推荐阅读