Delimited String Parser

DECLARE @delimitedData VARCHAR(MAX)
SET @delimitedData = 
'1023|Count of attempts|20
1045|Possible hacking|12
2010|Abandoned sessions|3231
2021|Hacked URLs|211'

CREATE TABLE #Array (ID INT PRIMARY KEY, [Description] VARCHAR(255), [Value] INT)

INSERT INTO #Array (ID, [Description], [Value])
SELECT Field1, Field2, Field3 FROM dbo.ufn_ParseDelimitedString(@delimitedData, '|', char(13)+char(10))

SELECT * FROM #Array

DROP TABLE #Array

OUTPUT from this is:

ID Description Value
==============================================
1023 Count of attempts 20
1045 Possible hacking 12
2010 Abandoned sessions 3231
2021 Hacked URLs 211

The function also handles missing (NULL) values:

DECLARE @delimitedData VARCHAR(MAX)
SET @delimitedData = 
'1023|Count of attempts|
1045||12
2010|Abandoned sessions|3231
2021|Hacked URLs|211'

CREATE TABLE #Array (ID INT PRIMARY KEY, [Description] VARCHAR(255), [Value] INT)

INSERT INTO #Array (ID, [Description], [Value])
SELECT Field1, Field2, Field3 FROM dbo.ufn_ParseDelimitedString(@delimitedData, '|', char(13)+char(10))

SELECT * FROM #Array

DROP TABLE #Array

OUTPUT IS:
ID Description Value
==============================================
1023 Count of attempts NULL
1045 NULL 12
2010 Abandoned sessions 3231
2021 Hacked URLs 211

-- ============================================================
--
-- Author:	Jano Petras
-- Create date: 09-Nov-2008
-- Description:	Parses a delimited string and produces a table
--
-- ============================================================
CREATE FUNCTION [dbo].[ufn_ParseDelimitedString] 
(
	@delimitedData VARCHAR(MAX),
	@fieldDelimiter VARCHAR(5),
	@recordDelimiter VARCHAR(5)
)
RETURNS @T TABLE(
	RecNo INT IDENTITY(1,1),
	Field1 VARCHAR(MAX), 
	Field2 VARCHAR(MAX), 
	Field3 VARCHAR(MAX), 
	Field4 VARCHAR(MAX), 
	Field5 VARCHAR(MAX), 
	Field6 VARCHAR(MAX), 
	Field7 VARCHAR(MAX),
	Field8 VARCHAR(MAX),
	Field9 VARCHAR(MAX))
AS
BEGIN
	-- Placeholder variables for fields
	DECLARE @F1 VARCHAR(MAX)
	DECLARE @F2 VARCHAR(MAX)
	DECLARE @F3 VARCHAR(MAX)
	DECLARE @F4 VARCHAR(MAX)
	DECLARE @F5 VARCHAR(MAX)
	DECLARE @F6 VARCHAR(MAX)
	DECLARE @F7 VARCHAR(MAX)
	DECLARE @F8 VARCHAR(MAX)
	DECLARE @F9 VARCHAR(MAX)

	-- loop controlling variables
	DECLARE @currentFieldValue VARCHAR(MAX) -- used to store value of a single field
	DECLARE @recordStartIndex INT -- the index where current record starts
	DECLARE @recordDelimiterIndex INT -- the index where record delimiter begins
	DECLARE @currentFieldIndex INT -- the index where current field starts
	DECLARE @currentFieldNumber INT -- the current field number (1 to 9)
	DECLARE @nextFieldDelimiter INT -- the index of the first field delimiter found after the current field start index

	-- initial positions
	SET @recordStartIndex = 1
	SET @recordDelimiterIndex = CHARINDEX(@recordDelimiter, @delimitedData, @recordStartIndex)
	-- this would be true if only 1 record
	IF @recordDelimiterIndex = 0 
		SET @recordDelimiterIndex = LEN(@delimitedData)+1
	SET @currentFieldIndex = @recordStartIndex
	
	-- master controlling loop
	WHILE (@recordStartIndex <= LEN(@delimitedData) AND @recordDelimiterIndex>0)
	BEGIN
		-- reset current record variables
		SET @F1 = NULL
		SET @F2 = NULL
		SET @F3 = NULL
		SET @F4 = NULL
		SET @F5 = NULL
		SET @F6 = NULL
		SET @F7 = NULL
		SET @F8 = NULL
		SET @F9 = NULL
		
		-- find the next field delimiter
		SET @nextFieldDelimiter = CHARINDEX(@fieldDelimiter, @delimitedData, @currentFieldIndex)
		-- if field delimiter was not found (a pipe for example), try to match the record delimiter (one and only field)
		IF @nextFieldDelimiter = 0
			SET @nextFieldDelimiter = CHARINDEX(@recordDelimiter, @delimitedData, @currentFieldIndex)
		-- did we go behind record boundary or have not found next delimiter?
		IF @nextFieldDelimiter > @recordDelimiterIndex OR @nextFieldDelimiter=0
			SET @nextFieldDelimiter = CHARINDEX(@recordDelimiter, @delimitedData, @currentFieldIndex) 

		-- if not luck, we could be on the last record
		IF @nextFieldDelimiter = 0
			SET @nextFieldDelimiter = @recordDelimiterIndex

		-- set the field number
		SET @currentFieldNumber = 1

		-- if it was found within the range of current record		
		WHILE (@currentFieldIndex BETWEEN @recordStartIndex AND @recordDelimiterIndex-1) AND (@nextFieldDelimiter BETWEEN @recordStartIndex AND @recordDelimiterIndex)
		BEGIN
			-- extract the field value
			SET @currentFieldValue = SUBSTRING(@delimitedData, @currentFieldIndex, @nextFieldDelimiter-@currentFieldIndex);
			IF LEN(@currentFieldValue)>0
			BEGIN
				IF @currentFieldNumber=1
					SET @F1 = @currentFieldValue
				ELSE IF @currentFieldNumber=2
					SET @F2 = @currentFieldValue
				ELSE IF @currentFieldNumber=3
					SET @F3 = @currentFieldValue
				ELSE IF @currentFieldNumber=4
					SET @F4 = @currentFieldValue
				ELSE IF @currentFieldNumber=5
					SET @F5 = @currentFieldValue
				ELSE IF @currentFieldNumber=6
					SET @F6 = @currentFieldValue
				ELSE IF @currentFieldNumber=7
					SET @F7 = @currentFieldValue
				ELSE IF @currentFieldNumber=8
					SET @F8 = @currentFieldValue
				ELSE IF @currentFieldNumber=9
					SET @F9 = @currentFieldValue
			END	
			-- prepare next iteration: iNcrease the field number
			SET @currentFieldNumber = @currentFieldNumber + 1
			
			-- find the next field delimiter index
			SET @currentFieldIndex = @nextFieldDelimiter+LEN(@fieldDelimiter)
			SET @nextFieldDelimiter = CHARINDEX(@fieldDelimiter, @delimitedData, @currentFieldIndex)

			-- did we go behind record boundary or have not found next delimiter?
			IF @nextFieldDelimiter > @recordDelimiterIndex OR @nextFieldDelimiter=0
				SET @nextFieldDelimiter = CHARINDEX(@recordDelimiter, @delimitedData, @currentFieldIndex) 

			-- if not luck, we could be on the last record
			IF @nextFieldDelimiter = 0
				SET @nextFieldDelimiter = @recordDelimiterIndex
				
		END
		
		-- the loop is finished, insert the record
		INSERT INTO @T (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
		VALUES (@F1, @F2, @F3, @F4, @F5, @F6, @F7, @F8, @F9)

		--
		-- Prepare for the next record
		--
		SET @recordStartIndex = @recordDelimiterIndex + LEN(@recordDelimiter)
		SET @currentFieldIndex = @recordStartIndex
		SET @currentFieldNumber = 1
		SET @recordDelimiterIndex = CHARINDEX(@recordDelimiter, @delimitedData, @recordStartIndex)
		IF @recordDelimiterIndex = 0 AND @recordStartIndex <= LEN(@delimitedData)
			SET @recordDelimiterIndex=LEN(@delimitedData)+1
	END

	RETURN
	
END