创建函数

1 SET ANSI_NULLS ON 2 GO 3  4 SET QUOTED_IDENTIFIER ON 5 GO 6  7 Create FUNCTION [dbo].[F_Split] 8  ( 9      @SplitString nvarchar(max),  --源字符串10      @Separator nvarchar(10)=' '  --分隔符号，默认为空格11  )12  RETURNS @SplitStringsTable TABLE  --输出的数据表13  (14      [id] int identity(1,1),15      [value] nvarchar(max)16  )17  AS18  BEGIN19      DECLARE @CurrentIndex int;20      DECLARE @NextIndex int;21      DECLARE @ReturnText nvarchar(max);22 23      SELECT @CurrentIndex=1;24      WHILE(@CurrentIndex<=len(@SplitString))25          BEGIN26              SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);27              IF(@NextIndex=0 OR @NextIndex IS NULL)28                  SELECT @NextIndex=len(@SplitString)+1;29                  SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);30                  INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);31                  SELECT @CurrentIndex=@NextIndex+1;32              END33      RETURN;34  END35 GO

 

　　使用：

select * from F_Split('1,2,3,4,5',',')

　　

select * from tb_SysUser u where u.ID in (select value from dbo.F_Split('1,2,3,4',','))