Sql中将字符串按分割符拆分 

创建函数
 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  AS
18  BEGIN
19      DECLARE @CurrentIndex int;
20      DECLARE @NextIndex int;
21      DECLARE @ReturnText nvarchar(max);
22 
23      SELECT @CurrentIndex=1;
24      WHILE(@CurrentIndex<=len(@SplitString))
25          BEGIN
26              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              END
33      RETURN;
34  END
35 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',','))

　　

posted @ 2017-06-13 13:44  我不是CC  阅读(36046)  评论(0)  编辑  收藏  举报