T-SQL中REPLACE函数“怪异”行为

问题描述

=================

客户尝试使用REPLACE函数来消除目标字符串变量中的指定子串，即以如下形式调用REPLACE函数(将指定子串替换为空字符串)：REPLACE(@v_NVARCHAR,@v_SubStr, '')

其中第一个参数即为NVARCHAR型的目标字符串。客户发现将不同类型的变量(NCHAR,NVARCHAR,CHAR,VARCHAR)作为REPLACE函数的第二个参数，会得到不同的结果;只有当REPLACE函数的第二个参数也是NVARCHAR类型时，才能得到正确的替换结果。

客户使用的测试脚本：

declare @com_m_address nvarchar(140)

select @com_m_address = N'新竹縣竹東鎮三重里鄰學府路巷弄號'

SELECT 'A', len(@com_m_address) as strLength ,

REPLACE(@com_m_address,convert(char(25),substring(@com_m_address,1,25)),'')

SELECT 'A', len(@com_m_address), --字串長度

REPLACE(@com_m_address,convert(varchar(25),substring(@com_m_address,1,25)),'')

SELECT 'A', len(@com_m_address) as strLength , --字串長度

REPLACE(@com_m_address,convert(nchar(25),substring(@com_m_address,1,25)),'');

--僅有Nvarchar 的測試結果有辦法取代成空白字元

SELECT 'A', len(@com_m_address) as strLength,

REPLACE(@com_m_address,convert(nvarchar(25), substring(@com_m_address,1,25)),'');

案例分析

===========

MSSQL中对于Unicode类型和ASCII类型的存储/使用不尽相同:

- MSSQL支持的Unicode字符实际上是UTF16(UCS2)，每个字符占用两个字节

- ASCII字符每个占用一个字节，为了表示不同的语言，所以ASCII字符必须对应某个字符集(Collation/Code Page)进行解释

因此可以将客户的测试脚本分成两部分来看：前两个针对ASCII字符串的测试、以及后两个针对Unicode字符串的测试。

1.ASCII字符串的测试/分析

将Unicode转换成ASCII是不推荐的，因为这可能造成乱码和数据丢失；事实上SQL 2008之前都不支持Unicode到ASCII的隐式转换，即使源字符串中并不存在非Unicode字符。

所以前两个测试得到异常结果跟REPLACE函数本身没有关系，问题出在字符串转换的过程，在这个过程中SQL将会做两件特别的事情：

1) 设置目标字符串的字符集。由于源字符串是UTF16、本身没有字符集的属性，所以目标字符串的字符集将被设置SQL实例的默认字符集。如果该SQL实例的默认字符集不支持中文(比如SQL_Latin_General)，则转换后的目标字符串(ASCII)将显示为乱码。

2) 目标字符串的二进制码将不同于源字符串(双字节字符单字节字符)，并且可能出现截断的情况。在客户的测试脚本中，他尝试将长度为16的UTF16字符串（占32个字节）转换为最大长度为25的CHAR/VARCHAR变量（最多存储25个字节），因此实际上源字符串中只有前12个字符被插入到目标字符串中。

我们可以使用如下脚本来观察UTF16字符串和ASCII字符串在字符长度/字节长度上的区别：

declare @com_m_address nvarchar(140)

--the only way to force tailing blanks for NCHAR variables is to explicitly append the blanks to the end of the string, notice the size here: 9 = 25-16

select @com_m_address = N'新竹縣竹東鎮三重里鄰學府路巷弄號' +REPLICATE(N' ',9)--字串長度

select LEN(@com_m_address), DATALENGTH(@com_m_address)

SELECT t.*, LEN(CvToChar) AS LenOfCvToChar, LEN(CvToVarChar) AS LenOfCvToVarChar,

LEN(CvToNChar) AS LenOfCvToNChar, LEN(CvToNVarChar) AS LenOfCvToNVarChar,

DATALENGTH(CvToChar) AS DLenOfCvToChar, DATALENGTH(CvToVarChar) AS DLenOfCvToVarChar,

DATALENGTH(CvToNChar) AS DLenOfCvToNChar, DATALENGTH(CvToNVarChar) AS DLenOfCvToNVarChar,

ASCII(SUBSTRING(CvToChar,13,1)) AS AscOfChar13, ASCII(SUBSTRING(CvToVarChar,13,1)) AS AscOfVarChar13

FROM

(SELECT len(@com_m_address) as strLength ,

convert(char(25), substring(@com_m_address,1,25)) AS CvToChar,

convert(varchar(25),substring(@com_m_address,1,25)) AS CvToVarChar,

convert(nchar(25), substring(@com_m_address,1,25)) AS CvToNChar,

convert(nvarchar(25), substring(@com_m_address,1,25)) AS CvToNVarChar) AS t

SELECT substring(@com_m_address,1,25) AS SubStr,

REPLACE(@com_m_address,convert(char(25),substring(@com_m_address,1,25)),'') AS TrimChar,

REPLACE(@com_m_address,convert(varchar(25),substring(@com_m_address,1,25)),'') AS TrimVarChar,

REPLACE(@com_m_address,convert(nchar(25),substring(@com_m_address,1,25)),'') AS TrimNChar,

REPLACE(@com_m_address,convert(nvarchar(25),substring(@com_m_address,1,25)),'') AS TrimNVarChar

从上面这个脚本我们可以看出以下几点：

1) 注意，这里在源字符串最后加了9个UTF16(双字节)的空格来填充NVARCHAR(140)这个变量的前25个字符，只有这样做才能使用转换为NCHAR(25)和NVARCHAR(25)的测试得到相同的预期结果。

2) 在转换为ASCII字符串之后，字符长度都为12；但是转换为CHAR(25)时，字节长度为25，尽管这其中只有前24个字节曾经用于字符拷贝和转换；

3) 转换为CHAR(25)之后，第13个字符为空格(ASCII码32)，这是定长字符串的填充字符，奇怪的是这个填充字符出现在转换过的子字符串后面(即第12个字符之后)而不是整个字符串的末尾。

2. Unicode字符串的测试/分析

从上面的分析我们知道，SQL对于变长字符串和定长字符串在初始化处理的时候是不一样的。对于客户的后两个测试，其结果不同主要是由于以下几个原因造成的：

1) 首先，NCHAR(25)和NVARCHAR(25)这两个变量在初始化申请空间的时候是不一样的。对于定长字符串，其字节长度在变量定义的时候就固定了；对于变长字符串，其字节长度是由变量中已经赋值的字符个数决定的。

2) 在NCHAR变量中，字符串尾部的空格都是被作为未初始化的空间来处理的；换句话说，SQL在初始化NCHAR变量的时候，会使用尾部空格来填充字符串字节空间(见上一节的测试脚本中对@com_m_address变量追加9个尾部空格的代码)。这个特征与SQL对于任何表中的NCHAR/NVARCHAR类型的列的ANSI_PADDING属性的处理是一致的：对于这种数据，其ANSI_PADDING属性总是为ON，即SQL会使用尾部空格对字符串进行填充。有趣的是，SQL对于ASCII数据(CHAR/VARCHAR)是允许用户控制ANSI_PADDING选项的，

关于这点可以参考如下测试脚本：

-- set this option OFF will cause the TrimChar and TrimVarChar to be the same result, as the CHAR(25) will NOT have tailing blanks

SET ANSI_PADDING OFF

declare @com_m_address varchar(140)

select @com_m_address = '新竹縣竹東鎮三重里鄰學府路巷弄號'

select LEN(@com_m_address), DATALENGTH(@com_m_address)

SELECT t.*, LEN(CvToChar) AS LenOfCvToChar, LEN(CvToVarChar) AS LenOfCvToVarChar,

LEN(CvToNChar) AS LenOfCvToNChar, LEN(CvToNVarChar) AS LenOfCvToNVarChar,

DATALENGTH(CvToChar) AS DLenOfCvToChar,

DATALENGTH(CvToVarChar) AS DLenOfCvToVarChar,

DATALENGTH(CvToNChar) AS DLenOfCvToNChar,

DATALENGTH(CvToNVarChar) AS DLenOfCvToNVarChar

FROM

(SELECT len(@com_m_address) as strLength ,

convert(char(25), substring(@com_m_address,1,25)) AS CvToChar,

convert(varchar(25),substring(@com_m_address,1,25)) AS CvToVarChar,

convert(nchar(25), substring(@com_m_address,1,25)) AS CvToNChar,

convert(nvarchar(25), substring(@com_m_address,1,25)) AS CvToNVarChar) AS t

SELECT substring(@com_m_address,1,25) AS SubStr,

REPLACE(@com_m_address,convert(char(25),substring(@com_m_address,1,25)),'') AS TrimChar,

REPLACE(@com_m_address,convert(varchar(25),substring(@com_m_address,1,25)),'') ASTrimVarChar,

REPLACE(@com_m_address,convert(nchar(25),substring(@com_m_address,1,25)),'') AS TrimNChar,

REPLACE(@com_m_address,convert(nvarchar(25),substring(@com_m_address,1,25)),'') ASTrimNVarChar

REPLACE函数在比较字符串的时候是对字符串中所有的字节进行比较，这是因为REPLACE还需要支持对二进制数据的替换。这意味着在对字符串使用REPLACE函数时，它不仅会比较可见字符和已初始化字符，即使对于NCHAR变量尾部未被初始化、但是被自动填充的空格也会严格进行比较。因此NCHAR(25)变量虽然包含与NVARCHAR(25)变量完全相同的子字符串，但是因为REPLACE函数以NCHAR(25)变量为第二个参数进行搜索时还会考虑尾部空格，因此客户的第三个测试得到的结果会不对。这也是为什么在上一节的测试脚本中对源字符串加上尾部空格的原因。

3.结论及建议：

虽然SQL的各个字符串类型看起来区别不大、很多时候也可以互相转换，但是SQL引擎内部对于每一种字符串变量类型的处理都是不同的，而且还会受到ANSI选项的影响。因此在使用字符串类型的时候应该注意以下几点：

- 不要将UTF16字符串转换成ASCII字符串

- 尽量使用变长的字符串类型

posted @ 2011-09-15 11:55 jyshi 阅读(1516) 评论(0) 编辑收藏举报

刷新页面返回顶部

T-SQL中REPLACE函数“怪异”行为

公告