Last active
January 26, 2017 20:25
-
-
Save JasonKleban/e8ea898c4e4fb71ca711937a2241a505 to your computer and use it in GitHub Desktop.
Deserialize a table of string values
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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