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

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.

• @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.

• Table(ID, Value)

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+

• Backlinks include_pagepage_componentbacklinks