SQL string[] Pack/Unpack utility functions
Can anyone skilled in t-sql create companion table-valued functions to these C# array pack & unpack utility methods that escape delimiters and preserve nulls, null arrays, empty arrays through roundtrip encoding?
By packing any variety of string arrays into a string, you can pass such (small) arrays to SQL as parameters, which is useful in some scenarios.
static class ArrayUtil
{
public static string Pack(string[] original)
{
return Pack(original, '|', '0', '~');
}
public static string[] Unpack(string original)
{
return Unpack(original, '|', '0', '~');
}
public static string Pack(string[] original, char delimiter, char zed, char escape)
{
if (delimiter == escape ||
zed == escape ||
delimiter == zed) throw new ArgumentException("special characters must be distinct");
// Null array returns a null string
if (original == null) return null;
// Empty array returns an empty string
if (original.Length == 0) return string.Empty;
// Arrays with a single empty element are represented as just the escape character
// to differentiate from an empty array
if (original.Length == 1 && original[0] == string.Empty) return escape.ToString();
// Otherwise
StringBuilder sb = new StringBuilder();
for (int i = 0, ol = original.Length; i < ol; i++)
{
string s = original[i];
if (s == null)
{
sb.Append(zed); // zed == null
}
else
{
for (int j = 0, sl = s.Length; j < sl; j++)
{
char c = s[j];
// escape literal delimiters, escapes, and leading zeds
if (c == delimiter ||
c == escape ||
(c == zed && j == 0)) sb.Append(escape);
sb.Append(c);
}
}
if (i != ol - 1) sb.Append(delimiter); // no trailing delimiter
}
return sb.ToString();
}
public static string[] Unpack(string original, char delimiter, char zed, char escape)
{
if (delimiter == escape ||
zed == escape ||
delimiter == zed) throw new ArgumentException("special characters must be distinct");
// Null string returns a null array
if (original == null) return null;
// Empty string returns an empty array
if (original == string.Empty) return new string[] { };
// A single escape character represents an array with a single empty element
// to differentiate from an empty array
if (original == escape.ToString()) return new string[] { string.Empty };
// Otherwise
StringBuilder sb = new StringBuilder(); // A place to store the current element
StringReader sr = new StringReader(original); // A stream of the original string
List<string> unpacked = new List<string>(); // The finished elements
int next;
while ((next = sr.Read()) >= 0)
{
char c = (char)next;
if (c == zed && sb.Length == 0)
{
unpacked.Add(null);
if ((next = sr.Peek()) >= 0 && (char)next != delimiter)
throw new ArgumentException("An element's leading zed character must be escaped or must alone be the element", "original");
sb = null;
}
else if (c == delimiter)
{
if (sb != null) unpacked.Add(sb.ToString());
sb = new StringBuilder();
}
else if (c == escape)
{
if ((next = sr.Read()) >= 0)
{
sb.Append((char)next);
}
else
throw new ArgumentException("Escapee expected", "original");
}
else
{
sb.Append(c);
}
}
// A final zed character will make sb = null, but otherwise we have an additional element
if (sb != null) unpacked.Add(sb.ToString());
return unpacked.ToArray();
}
}
Example packings with Pack(original, '|', '0', '~') , basic case:
["hello", "world"] -> "hello|world"
Some special cases (as discovered by PEX)
null -> null
[null] -> "0"
[null, null] -> "0|0"
[] -> ""
[""] -> "~"
["", ""] -> "|"
["|", "|"] -> "~||~|"
这里是对T-SQL Unpack的一个刺探：
CREATE FUNCTION [dbo].[UnpackStrings]
(
@original nvarchar(4000),
@delimiter nchar(1),
@zed nchar(1),
@escape nchar(1)
)
RETURNS
@unpacked TABLE
(
elementNumber INT IDENTITY(1,1),
element nvarchar(4000)
)
AS
BEGIN
DECLARE @next int;
DECLARE @c nchar(1);
DECLARE @pos int;
DECLARE @sb nvarchar(4000);
-- Special characters must be distinct.
IF ( (@delimiter = @escape) OR
(@zed = @escape) OR
(@delimiter = @zed) )
RETURN;
-- Null string return a null array
IF (@original IS NULL)
RETURN;
-- 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
-- Otherwise read through the string and unpack.
SET @pos = 1;
SET @sb = '';
SET @next = 0;
-- Fill the table variable with the rows for your result set
WHILE( (@pos <= LEN(@original)) AND @next IS NOT NULL )
BEGIN
SET @next = UNICODE(SUBSTRING(@original, @pos, 1));
IF (@next IS NULL)
BEGIN
IF (LEN(@sb) > 0)
INSERT @unpacked (element) VALUES (@sb);
SET @sb = '';
CONTINUE;
END
ELSE
BEGIN
SET @c = NCHAR(@next);
IF ( @c = @zed AND (LEN(@sb) = 0 OR LEN(@sb) IS NULL) )
BEGIN
INSERT @unpacked (element) VALUES (NULL);
-- need to peek at next character,
SET @next = UNICODE(SUBSTRING(@original, @pos+1, 1));
IF (@next IS NOT NULL)
BEGIN
SET @c = NCHAR(@next);
IF ( @c != @delimiter )
BEGIN
-- Peek at next character and it not delimiter,
-- bad format encountered.
BREAK;
END
END
SET @sb = NULL;
END
ELSE
BEGIN
IF ( @c = @delimiter )
BEGIN
IF (LEN(@sb) > 0 )
INSERT @unpacked (element) VALUES (@sb);
SET @sb = '';
END
ELSE
BEGIN
IF ( @c = @escape )
BEGIN
SET @pos = @pos + 1;
SET @next = UNICODE(SUBSTRING(@original, @pos, 1));
IF (@next IS NULL )
BEGIN
CONTINUE;
END
ELSE
BEGIN
SET @sb = @sb + NCHAR(@next);
END
END
ELSE
BEGIN
SET @sb = @sb + @c;
END
END
END
--
END
SET @pos = @pos + 1;
END
--
-- Likely not needed. This is handled above.
--
-- A final zed character will made sb = null,
-- but otherwise we have an additional element.
IF (@sb IS NOT NULL )
INSERT @unpacked (element) VALUES (@sb);
RETURN
END
上一篇: MySQL中的分层关系数据库设计
下一篇: SQL字符串[]打包/解压实用程序函数