Skip to content

Instantly share code, notes, and snippets.

@JasonKleban
Last active January 26, 2017 20:24
Show Gist options
  • Save JasonKleban/102788bd73f7f4f8b10807f567ab2ebd to your computer and use it in GitHub Desktop.
Save JasonKleban/102788bd73f7f4f8b10807f567ab2ebd to your computer and use it in GitHub Desktop.
Serialize a table of string values
/*
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