====== Description ======

The SQL function is a utility that is used by several SQL import methods. This function may be added and used as needed.

== Purpose ==

This function splits an input string into a table of rows. This function is built in starting with SQL 2016, but previous versions of SQL have no built in function to do this.

== Input ==

  * **@String**  - The input string to split.
  * **@Separator**  - The delimiter to use to split the strings by.
  * **@RemoveQuotes**  - An //optional//  bit, set this to 1 to indicate the routine should strip single or double quotes surrounding the segments.
== Output/Results ==

  * Table(ID, Value)

== Code ==

<code sql>
-- =============================================
-- Author:        Cyrious Sofware
-- Create date: May-2016
-- Description: This table function splits an input string into a table
--              table of values.  (This is native in SQL 2016, but
--              that isn't too much help yet.)
-- =============================================
ALTER FUNCTION [dbo].[csf_String_Split](
    @String NVARCHAR(MAX),
    @Separator NVARCHAR(64),
    @RemoveQuotes bit = 0
)
RETURNS
    @output TABLE( ID tinyint, VALUE VARCHAR(2000) )
BEGIN
    DECLARE @ssize tinyint = len(@Separator);
    DECLARE @counter tinyint = 1;
    DECLARE @START INT, @END INT;
    DECLARE @col VARCHAR(2000);
    SELECT @START = 1, @END = CHARINDEX(@separator, @string)
    WHILE @START < LEN(@string) + 1
    BEGIN
        IF @END = 0
            SET @END = LEN(@string) + 1;

        SET @col = SUBSTRING(@string, @START, @END - @START);

        IF (@RemoveQuotes = 1)
        BEGIN
            IF RIGHT(@col,1) = '"' AND LEFT(@col,1) = '"'
                SET @col = SUBSTRING(@col, 2, len(@col)-2 )
            ELSE IF RIGHT(@col,1) = '''' AND LEFT(@col,1) = ''''
                SET @col = SUBSTRING(@col, 2, len(@col)-2 );
        END;

        INSERT INTO @output (ID, VALUE)
        VALUES(@counter, @col)
        SET @START = @END + @ssize ;
        SET @END = CHARINDEX(@separator, @string, @START);
        SET @counter = @counter + 1;
    END
    RETURN
END
</code>

**Source**

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+