是否可以使用逗号分隔变量和TSQL IN运算符过滤记录列表？

本文介绍了是否可以使用逗号分隔变量和TSQL IN运算符过滤记录列表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

可以使用带有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))