USE [AutomationSandboxDB]
GO

/****** Object:  UserDefinedFunction [dbo].[SplitStrings]    Script Date: 3/5/2020 7:40:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- ============================================================
-- Author:		Michael Horvath
-- Create date: 3/04/2020
-- Description:	Split a string into parts separated by a delimiter
--              this is taken from https://sqlperformance.com/2012/07/t-sql-queries/split-strings
--              and enhanced with a minor bug fix for the single space ' ' delimiter.
--              Also added an ArrayIndex to the table returned by this function to allow
--              for 'indexing' into the resulting 'array'.  A final revision to this function
--              now allows empty strings to be a part of the 'array' returned by this function.
-- ============================================================
CREATE FUNCTION [dbo].[SplitStrings]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE ([ArrayIndex] int identity(1,1) NOT NULL, [Item] NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter), @LocalDelimiter NVARCHAR(255) = @Delimiter;

   -- Update where the length of delimiter is incremented corrected the problem where the function would never finish.
   -- Now whitespace only is allowed!  Kept the comments from the earlier version as an example of throwing an exception from
   -- a user defined function.

   -- Validate the delimiter value passed into the function and force an exception if it is whitespace only such as ' '.
   IF @LocalDelimiter = ' '
   BEGIN
        SET @ld = @ld + 1
		SET @ll = @ll + 1
		-- Throw not allowed in a user function so cause an error by attempting an invalid cast operation.
		--DECLARE @valueInt INT = cast('Here is the real ERROR: "Invalid value for @Delimiter = ''' + @LocalDelimiter + ''', but cannot be whitespace only!"' as int);
   END;
 
   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   -- WHERE LEN([value]) > 0 -- Commented this out to allow for split to function more like string Split function in C# where empty strings can be part of the
                             -- resulting array.
   OPTION (MAXRECURSION 0);
 
   RETURN;
END










GO