====== 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_formatsql]]



-- =============================================



-- 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+



====== See Also ======
  * Backlinks [[include_pagepage_componentbacklinks]]