巧用函数，使Sql中in的用法更多变

最新推荐文章于 2023-08-25 15:29:01 发布

Joe-Fan

最新推荐文章于 2023-08-25 15:29:01 发布

阅读量550

点赞数

本文链接：https://blog.csdn.net/fanxingthink/article/details/9306349

版权

在Sql中我们经常会用到in

普遍的写法为

where xx in ('1','2','3')

通过函数写法为:

IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ArrayToTable]') 
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
DROP FUNCTION [dbo].[ArrayToTable]
GO

create function ArrayToTable(@arrayList varchar(2000),@split varchar(2))
returns @result table(subscript int,value char(200))
as
 begin
 declare @i int,
 @index int
 set @i = 0
 set @index = charindex(@split,@arrayList)
 while(@index <> 0)
 begin
 insert into @result(subscript,value)
 values(@i,substring(@arrayList,1,@index-1))
 set @arrayList = stuff(@arrayList,1,@index,'')
 set @index = charindex(@split,@arrayList)
 set @i = @i+1
 end
 insert into @result(subscript,value)
 values(@i,@arrayList)
 return
 end

declare @lineData varchar(200)
set @lineData='1,2,3,4'

where (@lineData is null or XX in (select value from dbo.ArrayToTable(@lineData,',')))