Create a function
1 SETAnsi_nulls on2 GO3 4 SETQuoted_identifier on5 GO6 7 Create FUNCTION [dbo].[F_split]8 (9 @SplitString nvarchar(Max),--Source StringTen @Separator nvarchar(Ten)=' ' --delimited symbols, default to spaces One ) A RETURNS @SplitStringsTable TABLE --data table for output - ( - [ID] int Identity(1,1), the [value] nvarchar(Max) - ) - as - BEGIN + DECLARE @CurrentIndex int; - DECLARE @NextIndex int; + DECLARE @ReturnText nvarchar(Max); A at SELECT @CurrentIndex=1; - while(@CurrentIndex<=Len(@SplitString)) - BEGIN - SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); - IF(@NextIndex=0 OR @NextIndex is NULL) - SELECT @NextIndex=Len(@SplitString)+1; in SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); - INSERT into @SplitStringsTable([value])VALUES(@ReturnText); to SELECT @CurrentIndex=@NextIndex+1; + END - RETURN; the END * GO
Use:
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 ', ', '))
Splitting a string into a split character in SQL