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