function customization for string interception

 SETAnsi_nulls onGOSETQuoted_identifier onGO/**************** Split string ****************/CREATE function [dbo].[splitstring](@Input nvarchar(Max),@Separator nvarchar(Max)=',', @RemoveEmptyEntries bit=1 )returns @TABLE Table ([Id] int Identity(1,1),[Value] nvarchar(Max))  asbegin Declare @Index int,@Entry nvarchar(Max)Set @Index = charindex(@Separator,@Input) while(@Index>0)beginSet @Entry=LTrim(RTrim(substring(@Input,1,@Index-1)))if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"')beginInsert  into @TABLE([Value])Values(@Entry)EndSet @Input = substring(@Input,@Index+datalength(@Separator)/2,Len(@Input))Set @Index = charindex(@Separator,@Input)EndSet @Entry=LTrim(RTrim(@Input))if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"')beginInsert  into @TABLE([Value])Values(@Entry)EndreturnEND

Calling the created SQL function

Declare @s varchar( -),@sql varchar( +),@split VARCHAR(Ten),@index INTSET @split='|';SET @index=1;SET @s='|21|2106|';Select COUNT(*) from [dbo].[splitstring](@s,@split,1)Select *  from [dbo].[splitstring](@s,@split,1)

Results

function customization for string interception