问题描述

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

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

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

客户使用的测试脚本：

declare @com_m_address nvarchar ( 140 )

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 的測試結果有辦法取代成空白字元

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

案例分析

===========

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

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

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

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

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

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

我们可以使用如下脚本来观察 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

( 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

( 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

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

3. 结论及建议：

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

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

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

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员