Skip to content

Instantly share code, notes, and snippets.

@JasonKleban
Last active January 26, 2017 20:25
Show Gist options
  • Save JasonKleban/e8ea898c4e4fb71ca711937a2241a505 to your computer and use it in GitHub Desktop.
Save JasonKleban/e8ea898c4e4fb71ca711937a2241a505 to your computer and use it in GitHub Desktop.
Deserialize a table of string values
CREATE FUNCTION [dbo].[UnpackStrings]
(
@original NVARCHAR(MAX)
,@delimiter NCHAR(1)
,@zed NCHAR(1)
,@escape NCHAR(1)
)
RETURNS
@Unpacked TABLE
(
ElementNumber INT IDENTITY(1,1)
,Element NVARCHAR(MAX)
)
AS BEGIN
DECLARE @curr NCHAR(1);
DECLARE @next NCHAR(1);
DECLARE @pos INT = 1;
DECLARE @sb NVARCHAR(MAX) = '';
-- Special characters must be distinct.
IF ((@delimiter = @escape) OR
(@zed = @escape) OR
(@delimiter = @zed)) BEGIN
SET @sb = CAST('Special characters must be distinct.' AS INT); -- (Can't throw or raiserror in a function)
END
-- Null string return a null array
IF (@original IS NULL) BEGIN
SET @sb = CAST('You must check for a null-value @original prior to calling this function.' AS INT); -- (Can't return a NULL valued table in SQL)
END
-- A single escape character represents an array with a single
-- empty element to differentiate from an empty array.
IF (@original = @escape) BEGIN
INSERT @unpacked (Element) VALUES ('');
RETURN;
END
-- Fill the table variable with the rows for your result set
WHILE((@pos <= LEN(@original))) BEGIN
SET @curr = SUBSTRING(@original, @pos, 1);
IF (@curr = @zed AND (LEN(@sb) = 0 OR LEN(@sb) IS NULL)) BEGIN
IF (COALESCE(NCHAR(UNICODE(SUBSTRING(@original, @pos + 1, 1))), @delimiter) != @delimiter) BEGIN -- Peek
SET @sb = CAST('Bad format - ambiguous unescaped zed encountered.' AS INT); -- (Can't throw or raiserror in a function)
END
SET @sb = NULL;
END ELSE IF (@curr = @delimiter) BEGIN
INSERT @unpacked (Element) VALUES (@sb);
SET @sb = '';
END ELSE IF (@curr = @escape) BEGIN
SET @pos = @pos + 1;
IF (LEN(@original) < @pos) BEGIN
SET @sb = CAST('Bad format - dangling escape character encountered.' AS INT);
END ELSE BEGIN
SET @sb = @sb + SUBSTRING(@original, @pos, 1);
END
END ELSE BEGIN
SET @sb = @sb + @curr;
END
SET @pos = @pos + 1;
END
INSERT @unpacked (Element) VALUES (@sb);
RETURN
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment