SQL跳字查询，求大神指导

[weixin_36785129]

现在想实现一共功能跳字查询，
比如我有记录
code name
01 张三李四
02 张三李四王五
03 张李四王五
04 张三王五
05 李四王五赵六



我sql查询的时候输入“张李” 可以查询出如下三条记录
code name
01 张三李四
02 张三李四王五
03 张李四王五


求存储过程或者函数

[卖水果的net]

拆成多个条件，最后再and

[学海无涯-回头是岸]

 with tb(code,NAME) AS ( 
 SELECT '01','张三李四' UNION ALL
  SELECT '02','张三李四王五' UNION ALL
  SELECT'03','张李四王五' UNION ALL
    SELECT'04', '张三王五')
SELECT * FROM TB WHERE NAME LIKE'%张%' and NAME LIKE'%李%'

[道素]

仅供参考，遍历条件每个字是否存在名字里，如果存在为0 否则为1，那么所有字结果合计大于0说明有个字不匹配

DECLARE @search NVARCHAR(1000)=N'张李'
;with tb(code,NAME) AS ( 
 SELECT '01',N'张三李四' UNION ALL
 SELECT '02',N'张三李四王五' UNION ALL
 SELECT '03',N'张李四王五' UNION ALL 
 SELECT '03',N'李四张王五' UNION ALL
 SELECT '04',N'张三王五'
 )
 SELECT * FROM tb 
 CROSS APPLY (
   SELECT SUM(s) s FROM (
       SELECT CASE WHEN CHARINDEX(SUBSTRING(@search,sv.number,1),tb.NAME)=0 THEN 1 ELSE 0 END AS s
    FROM master.dbo.spt_values AS sv where sv.type='P' AND sv.number BETWEEN 1 AND LEN(@search)
  ) o
) t
WHERE t.s=0

[gw6328]

这个要看你具体了， 如果 像张李 ，分成 张和李 来查询不知道是否江你的要求，如果是 张三李，三个字呢？这个要看你需求。

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(22),[name] nvarchar(26))
Insert #T
select N'01',N'张三李四' union all
select N'02',N'张三李四王五' union all
select N'03',N'张李四王五' union all
select N'04',N'张三王五' union all
select N'05',N'李四王五赵六'
GO
DECLARE @Where NVARCHAR(100)='张李'

DECLARE @i INT=LEN(@Where)

WHILE @i>0
	SELECT @Where=STUFF(@Where,@i,0,'%'),@i=@i-1
Select * from #T WHERE PATINDEX(@Where+'%',[name])>0

/*
code	name
01	张三李四
02	张三李四王五
03	张李四王五
*/

[baidu_35289351]

引用 5 楼 roy_88 的回复:

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(22),[name] nvarchar(26))
Insert #T
select N'01',N'张三李四' union all
select N'02',N'张三李四王五' union all
select N'03',N'张李四王五' union all
select N'04',N'张三王五' union all
select N'05',N'李四王五赵六'
GO
DECLARE @Where NVARCHAR(100)='张李'

DECLARE @i INT=LEN(@Where)

WHILE @i>0
	SELECT @Where=STUFF(@Where,@i,0,'%'),@i=@i-1
Select * from #T WHERE PATINDEX(@Where+'%',[name])>0

/*
code	name
01	张三李四
02	张三李四王五
03	张李四王五
*/

完全正确，在SQL2000上都通过。

[baidu_35289351]

TO:中国风 你这段话的精辟在这 WHILE @i>0 SELECT @Where=STUFF(@Where,@i,0,'%'),@i=@i-1 还能这样使用的。。。

[道素]

如果name是是 李张 需不需要也出来呢？ 如果顺序固定用 like N'%[张]%[李]%' 也可以

[中国风]

引用 7 楼 baidu_35289351 的回复:

TO:中国风 你这段话的精辟在这 WHILE @i>0 SELECT @Where=STUFF(@Where,@i,0,'%'),@i=@i-1 还能这样使用的。。。

用“%”符号间隔第一个字 生成这样的格式：%张%李 再用到条件时结尾加上多个:% --字符串最终要实现这样的条件格式：%张%李%

[baidu_35289351]

引用 9 楼 roy_88 的回复:

[quote=引用 7 楼 baidu_35289351 的回复:] TO:中国风 你这段话的精辟在这 WHILE @i>0 SELECT @Where=STUFF(@Where,@i,0,'%'),@i=@i-1 还能这样使用的。。。

用“%”符号间隔第一个字 生成这样的格式：%张%李 再用到条件时结尾加上多个:% --字符串最终要实现这样的条件格式：%张%李%[/quote] 斑竹，我发现了个问题，如 name='你们很好吗' 当 @where='你很吗'，是可以查出来 但@where='你吗很'，就查不出来了。 还能优化不？

[中国风]

对，看上面例子说明，那是有顺序的，如果打乱顺序去查，需拆分再匹配

[中国风]

写个例子,SQL2000用辅助表，这样的实现最好用全文检索去实现性能高些 e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(22),[name] nvarchar(26))
Insert #T
select N'01',N'张三李四' union all
select N'02',N'张三李四王五' union all
select N'03',N'张李四王五' union all
select N'04',N'张三王五' union all
select N'05',N'李四王五赵六'
GO
--生成辅助表TNum
SELECT TOP 1000 Num=IDENTITY(INT,1,1) INTO TNum FROM syscolumns AS a,syscolumns AS b
go
DECLARE @Where NVARCHAR(100)='李张'
SELECT * from #T AS a WHERE NOT EXISTS(SELECT 1 FROM TNum AS b WHERE LEN(@Where)>=b.Num AND NOT EXISTS(SELECT 1 FROM #T WHERE [code]=a.[code] AND [name] LIKE '%'+SUBSTRING(@Where,b.Num,1)+'%'))

/*
code	name
01	张三李四
02	张三李四王五
03	张李四王五
*/