当前位置:Gxlcms > 数据库问题 > Sql中将字符串按分割符拆分

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

时间:2021-07-01 10:21:17 帮助过:462人阅读

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‘,‘,‘))

  

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

标签:div   _id   func   ges   into   ansi   max   user   ide   

人气教程排行