MS SQL BUG：无效的REPLACE函数 - 捷克排序规则 - 在哪里可以为REPLACE无效结果创建问题？ [英] MS SQL BUG: Invalid REPLACE function - czech collations - Where can I create issue for the REPLACE invalid result?

问题描述

问题：我在哪里可以为REPLACE无效结果创建问题？

REPLACE 函数在使用

SELECT

SELECT 

REPLACE（N'Chachach'COLLATE）时使用Czech_CI_AI（及类似）排序规则返回无效结果Latin1_General_BIN，N'''，N'＃'）， 

REPLACE（N'Chachach'COLLATE Czech_CI_AI，N'''，N'＃'）

结果：

Chachach #hachach

起始C（h）字符（无论是大写/小写）与字符N'ጠ'（unicode 0x1320）匹配。

在捷克语中CH被认为是单个字母，即使编码为4个字节，C和CH的排序也不同。

此REPLACE函数行为似乎与特定的捷克排序规则相关，而不是与SQL SERVER版本相关。我已经在 

• Microsoft SQL Server 2017（RTM-GDR）（KB4293803） - 14.0.2002.14 
• Microsoft进行了测试SQL Server 2016（SP2-GDR）（KB4293802） - 13.0.5081.1

• Microsoft SQL Server 2014（SP2-GDR）（KB4019093） - 12.0.5207.0

解决方法很明显，事实上，这是一个小错误，如果有的话。

我的完整测试代码：

声明@searchChar nvarchar（1）= NCHAR （0x1320）;
$


SELECT 

UNICODE（@ searchChar）为'@ searchChar UNICODE'，

'0x'+ FORMAT（UNICODE（@ searchChar），'x4'）为'@ searchChar HEX UNICODE'，

@searchChar为'@ searchChar'，

REPLACE（ N'Chachach'COLLATE Latin1_General_BIN，@ searchChar，N'＃'）为Latin1_General_BIN， 

REPLACE（N'Chachach'COLLATE Czech_CS_AS，@ searchChar，N'＃'）为Czech_CS_AS，

REPLACE（N'Chachach'COLLATE Czech_CS_AI，@ sear chChar，N'＃'）为Czech_CS_AI，

REPLACE（N'Chachach'COLLATE Czech_CI_AI，@ searchChar，N'＃'）为Czech_CI_AI，

REPLACE（N' Chachach'COLLATE Czech_CI_AS，@ searchChar，N'＃'）为Czech_CI_AS，

REPLACE（N'Chachach'COLLATE Czech_CS_AS，@ searchChar，N'＃'）为Czech_CS_AS，

REPLACE（N'Chachach'COLLATE Czech_CS_AI，@ searchChar，N'＃'）为Czech_CS_AI，

REPLACE（N'Chachach'COLLATE Czech_CI_AI，@ searchChar，N'＃'）为Czech_CI_AI，

REPLACE（N'Chachach'COLLATE Czech_CI_AS，@ searchChar，N'＃'）为Czech_CI_AS



SELECT @@ VERSION;



选择'SELECT REPLACE（N''Chachach''COLLATE'+  name +'，@ searchChar，N''＃''）as ['+ name +']'

FROM fn_helpcollat​​ions（）  

解决方案

我在哪里可以为REPLACE无效结果创建问题？

https://feedback.azure.com/forums/908035-sql-server

THE QUESTION: Where can I create issue for the REPLACE invalid result?

The REPLACE function returns invalid result when using Czech_CI_AI (and similar) collations for the

SELECT 
REPLACE(N'Chachach' COLLATE Latin1_General_BIN, N'ጠ', N'#'), 
REPLACE(N'Chachach' COLLATE Czech_CI_AI, N'ጠ', N'#')

The result:

Chachach #hachach

The starting C(h) character (regardless if in upper/lower case) is matched with the character N'ጠ' (the unicode 0x1320).

In Czech CH is considered as single letter even though coded as 4 bytes, sorting is different for C and CH.

This REPLACE function behaviour seems to be related to the specific czech collation(s), not to SQL SERVER version. I have tested this on 

• Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 
• Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1

• Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0

The workaround is clear, in fact, that's a minor bug, if ever.

My complete testing code:

declare @searchChar nvarchar(1) = NCHAR(0x1320);

SELECT 
UNICODE(@searchChar) as '@searchChar UNICODE',
'0x' + FORMAT(UNICODE(@searchChar),'x4') as '@searchChar HEX UNICODE',
@searchChar as '@searchChar',
REPLACE(N'Chachach' COLLATE Latin1_General_BIN, @searchChar, N'#') as Latin1_General_BIN, 
REPLACE(N'Chachach' COLLATE Czech_CS_AS, @searchChar, N'#') as Czech_CS_AS,
REPLACE(N'Chachach' COLLATE Czech_CS_AI, @searchChar, N'#') as Czech_CS_AI,
REPLACE(N'Chachach' COLLATE Czech_CI_AI, @searchChar, N'#') as Czech_CI_AI,
REPLACE(N'Chachach' COLLATE Czech_CI_AS, @searchChar, N'#') as Czech_CI_AS,
REPLACE(N'Chachach' COLLATE Czech_CS_AS, @searchChar, N'#') as Czech_CS_AS,
REPLACE(N'Chachach' COLLATE Czech_CS_AI, @searchChar, N'#') as Czech_CS_AI,
REPLACE(N'Chachach' COLLATE Czech_CI_AI, @searchChar, N'#') as Czech_CI_AI,
REPLACE(N'Chachach' COLLATE Czech_CI_AS, @searchChar, N'#') as Czech_CI_AS

SELECT @@VERSION;

SELECT 'SELECT REPLACE(N''Chachach'' COLLATE ' +  name +', @searchChar, N''#'') as [' + name + ']'
FROM fn_helpcollations()  

解决方案

Where can I create issue for the REPLACE invalid result?

At https://feedback.azure.com/forums/908035-sql-server

这篇关于MS SQL BUG：无效的REPLACE函数 - 捷克排序规则 - 在哪里可以为REPLACE无效结果创建问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！