TSQL: Splitting a String

Table-valued Function:

create FUNCTION [dbo].[SplitString]
(
 @input nvarchar(max),
@separator nvarchar(max)
)
RETURNS @splits TABLE
(
split nvarchar(max)
 , position int
)

AS

BEGIN
SET @separator = ISNULL(@separator,N',');
DECLARE @seplen INT;
SET @seplen = LEN(@separator);

 DECLARE @remainder nvarchar(max);
 SET @remainder = ISNULL(@input,N'');
 DECLARE @count int;
 SET @count = 0;

 WHILE ( len(@remainder) > 0 )
 BEGIN
 DECLARE @split nvarchar(max);
 DECLARE @pos int;
 SET @pos = CHARINDEX( @separator, @remainder, -1 );

 IF @pos > 0
  BEGIN
         SET @split = LEFT( @remainder, @pos-1 );
         SET @remainder = RIGHT( @remainder, LEN(@remainder)-(@pos+@seplen-1) );
 END
 ELSE
 BEGIN
         SET @split = @remainder;
         SET @remainder = N'';
 END

 SET @count = @count + 1;

 INSERT INTO @splits(split,position) VALUES (LTRIM(RTRIM(@split)),@count);
END

 RETURN;

END


Using the function:

select * from [dbo].[SplitString] ('a,bc,de, fg', ',')


Output:









------------------------------------------------------
Another Sample

Table:
 

Script:

select distinct dockey, splits.split 'BusinessProcess', penaltyinformation
 from Penalties p
 outer apply dbo.SplitString(BusinessProcess, N',') splits


Output: