是否可以使用逗号分隔变量和TSQL IN运算符过滤记录列表？ [英] Can a list of records be filtered using a comma delimited variable with the TSQL IN operator?

问题描述

可以使用带有TSQL IN运算符的逗号分隔变量过滤记录列表吗？



类似

Can a list of records be filtered using a comma delimited variable with the TSQL IN operator?

like

declare @val nvarchar(max)
set @val=  'E1+E2+E3'
set @val=REPLACE(@val,'E','')
set @val=REPLACE(@val,'+',',')
select * from empmas where headcode in(@val)

推荐答案

是的它可以是例如

yes it can be for example

select * from Employee where Salary in(5000,8000,15000,40000)

您可以使用广泛使用的fnsplitter函数拆分t sql ...你必须将这个功能添加到你的数据库...

You can use the fnsplitter function which is used widely for splitting in t sql... you have to add this function to your database...

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Function [dbo].[fnSplitter] (@IDs Varchar(4000) )  
Returns @Tbl_IDs Table  (ID nvarchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS  )  As  

Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @Pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<len(@ids)>
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Substring(@IDs,@Pos2,@Pos1-@Pos2) 
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

将您的查询更改为

after adding change your query to

Declare @val nvarchar(max)
Set @val=  'E1+E2+E3'
Set @val=REPLACE(@val,'E','')
Set @val=REPLACE(@val,'+',',')
Select * from empmas where headcode in(Select ID From fnSplitter(@Val))

这篇关于是否可以使用逗号分隔变量和TSQL IN运算符过滤记录列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！