在 nvarchar 比较中，0x8FFF 的意义是什么？

Question

在 nvarchar 比较中，0x8FFF 的意义是什么？

4

在SQL Server中，nvarchar值表示Unicode码点的字符串 - 默认情况下，使用UTF-16，超过0xFFFF的值将表示为代理对。

我想为一个nvarchar UDF参数设置默认字符串值，其中包含特殊字符。T-SQL不允许在字符串字面量中使用十六进制转义序列，必须使用CHAR()或NCHAR()函数按其代码点值指定字符，但是您必须使用字面值作为参数默认值：您不能使用NCHAR()。然而，我记得SQL Server还执行从varbinary到nvarchar的隐式转换，因此：

CREATE FUNCTION DoSomething(
    @foo nvarchar(50) = '\x0008', -- not supported by T-SQL syntax
    @bar nvarchar(50) = NCHAR(8), -- forbidden: defaults must be a literal
    @baz nvarchar(50) = 0x008     -- success!
)

我想要更改参数来表示比较范围，并且我希望默认值表示最广泛的价值范围，这样让我使用静态SQL进行搜索功能而不需要OPTION(RECOMPILE)或现在被否定的(@foo IS NULL OR Table.Foo = @foo)模式。

所以我把我的函数改成了这样：

CREATE FUNCTION DoSomething(
    @fooMin nvarchar(50) = 0x0000,
    @fooMax nvarchar(50) = 0xFFFF
)
/* SELECT goes here */
WHERE
    Foo BETWEEN @fooMin AND @fooMax

我认为使用0xFFFF作为上限应该足以容纳任何（实际）Unicode文本输入到我正在构建的系统中。

然而，出乎意料的是，BETWEEN运算符始终返回false。我想知道是否与上限操作数有关，所以我将其更改为0x7FFF，这样它就可以正常工作。

接下来，我尝试了0x8FFF，也可以正常工作。

但0x9FFF和0x9000失败了。

据我所知，0x8FFF - 0x9000边界在Unicode中没有什么特别之处。维基百科报告称基本多语言平面占用0x0000 - 0xFFFF，而0x900只是CJK区域的另一个块：https://en.wikipedia.org/wiki/Plane_(Unicode)#/media/File:Roadmap_to_Unicode_BMP.svg，UTF-16代理项从0xD800和0xDC00开始，远离0x900。

以下是我的测试用例：

SELECT N'HELLO', 0xFF, (如果N'HELLO'在0x0000和0xFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x0FFF, (如果N'HELLO'在0x0000和0x0FFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x1000, (如果N'HELLO'在0x0000和0x1000之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x6000, (如果N'HELLO'在0x0000和0x6000之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x6FFF, (如果N'HELLO'在0x0000和0x6FFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x7000, (如果N'HELLO'在0x0000和0x7000之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x7FFF, (如果N'HELLO'在0x0000和0x7FFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x8000, (如果N'HELLO'在0x0000和0x8000之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x8FFF, (如果N'HELLO'在0x0000和0x8FFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x9000, (如果N'HELLO'在0x0000和0x9000之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0x9FFF, (如果N'HELLO'在0x0000和0x9FFF之间，则为'yup'，否则为'no') UNION ALL SELECT N'HELLO', 0xFFFF, (如果N'HELLO'在0x0000和0xFFFF之间，则为'yup'，否则为'no')

结果如下：
HELLO   0xFF    yup
HELLO   0x0FFF  no
HELLO   0x1000  no
HELLO   0x6000  no
HELLO   0x6FFF  yup
HELLO   0x7000  yup
HELLO   0x7FFF  yup
HELLO   0x8000  no
HELLO   0x8FFF  yup
HELLO   0x9000  no
HELLO   0x9FFF  no
HELLO   0xFFFF  no

所以看起来不仅仅是0x7FFF - 0x8000的边界问题，还有其他边界问题。

我想知道是否是因为将二进制文本解释为小端而不是大端，但是以**FF结尾的所有文本都会返回true，因为它们比N'H'大。

- Dai

你正在忽略在SQL中定义排序顺序的*校对(collations)。如果你强制使用二进制(binary)*校对，你会发现大多数范围比较都能正常工作。 - Damien_The_Unbeliever

“我认为0xFFFF足以容纳我正在构建的系统中出现的任何（实际的）Unicode文本，只要您不必处理东亚文本、表情符号、符号等。这些需要在UTF-16中使用代理对。” - Remy Lebeau

那么，将参数默认设置为NULL，并让您的函数在内部检查它们如何？然后您就不需要在参数声明中使用实际字符串文字，并且可以随时间更改默认值而无需更改声明。 - Remy Lebeau

@RemyLebeau 这将导致一个次优的SQL Server执行计划：http://www.sommarskog.se/dyn-search.html - Dai

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jim31415 · Accepted Answer

在进行比较测试之前，将您的字段转换为相同的类型：

select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x0000 AND 0xffff THEN 'yup' ELSE 'no' END
select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x4800 AND 0xffff THEN 'yup' ELSE 'no' END
select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x4800 AND 0x4801 THEN 'yup' ELSE 'no' END

或者

declare @x1 nvarchar(2) = 0x4800, @x2 nvarchar(2) = 0xFFFF;
declare @l1 nvarchar(2) = reverse(convert(varbinary(2), @x1));
declare @l2 nvarchar(2) = reverse(convert(varbinary(2), @x2));
select CASE WHEN N'HELLO' BETWEEN @l1 AND @l2 THEN 'yup' ELSE 'no' END