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
-- ============================================= -- 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
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+
You could leave a comment if you were logged in.