Last active
January 26, 2017 20:24
-
-
Save JasonKleban/102788bd73f7f4f8b10807f567ab2ebd to your computer and use it in GitHub Desktop.
Serialize 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 TYPE [dbo].[StringArray] AS TABLE ( | |
ElementNumber INT IDENTITY(1,1) | |
,[Element] NVARCHAR(MAX) | |
) | |
*/ | |
CREATE FUNCTION [dbo].[PackStrings] | |
( | |
@original [dbo].[StringArray] READONLY | |
,@delimiter NCHAR(1) | |
,@zed NCHAR(1) | |
,@escape NCHAR(1) | |
) RETURNS NVARCHAR(MAX) AS BEGIN | |
DECLARE @next INT; | |
DECLARE @pos INT; | |
DECLARE @sb NVARCHAR(MAX) = ''; | |
DECLARE @sb_ NVARCHAR(MAX) = ''; | |
DECLARE @element NVARCHAR(MAX); | |
-- Special characters must be distinct. | |
IF ( (@delimiter = @escape) OR | |
(@zed = @escape) OR | |
(@delimiter = @zed) ) | |
RETURN CAST('Special characters must be distinct.' AS INT); -- (Can't throw or raiserror in a function) | |
-- Null string return a null array -- custom table type cannot be null value | |
-- IF (@original IS NULL) | |
-- RETURN NULL; | |
-- A single escape character represents an array with a single | |
-- empty element to differentiate from an empty array. | |
IF ((SELECT COUNT(0) FROM @original) = 1 AND (SELECT COUNT(0) FROM @original WHERE Element = '') = 1) BEGIN | |
RETURN ''; | |
END | |
DECLARE original_cursor CURSOR FOR SELECT Element FROM @original | |
OPEN original_cursor | |
FETCH NEXT FROM original_cursor INTO @element | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @pos = 1 | |
SET @sb_ = '' | |
SET @next = 0 | |
IF (@element IS NULL) BEGIN | |
SET @sb_ = @zed | |
END ELSE BEGIN | |
WHILE((@pos <= LEN(@element)) AND @next IS NOT NULL) BEGIN | |
SET @next = UNICODE(SUBSTRING(@element, @pos, 1)); | |
IF (NCHAR(@next) IN (@delimiter, @escape) OR (NCHAR(@next) = @zed AND @pos = 1)) BEGIN | |
SET @sb_ = @sb_ + @escape | |
END | |
SET @sb_ = @sb_ + NCHAR(@next) | |
SET @pos = @pos + 1 | |
END | |
END | |
SET @sb = @sb + @sb_ | |
FETCH NEXT FROM original_cursor INTO @element | |
IF (@@FETCH_STATUS = 0) | |
SET @sb = @sb + @delimiter | |
END | |
CLOSE original_cursor; | |
DEALLOCATE original_cursor; | |
RETURN @sb | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment