SQL Replace 後，怎麼會留空白呢?

問題

最近客戶將 SQL Server 從 SQL 2012 升級到 SQL 2016 後，有些 Store Procedure 居然發生了錯誤。
結果發現，原本在 SQL 2012 中去 Replace 後，原本後面有空字串的會一併 RTrim 掉，但在 SQL 2016 中，那些空白就會留著。

怎麼會這樣子呢? 查看官方文件中，這個行為在 SQL 2008 之後就是如此了呀~
Behavior Changes to Database Engine Features in SQL Server 2008 - REPLACE Function

In SQL Server 2005, trailing spaces specified in the first input parameter to the REPLACE function are trimmed when the parameter is of type char. For example, in the statement SELECT ‘<’ + REPLACE(CONVERT(char(6), ‘ABC ‘), ‘ ‘, ‘L’) + ‘>’, the value ‘ABC ‘ is incorrectly evaluated as ‘ABC’.

In SQL Server 2008, trailing spaces are always preserved. For applications that rely on the previous behavior of the function, use the RTRIM function when specifying the first input parameter for the function. For example, the following syntax will reproduce the SQL Server 2005 behavior SELECT ‘<’ + REPLACE(RTRIM(CONVERT(char(6), ‘ABC ‘)), ‘ ‘, ‘L’) + ‘>’.

也就是說，SQL 2008 之後，如果要將 Replace 後的空字串 Trim 掉，請自已加一下 RTRIM !!!

嗯，不過，我們的現況是 SQL 2012 跟 SQL 2016 ，這到底是怎麼回事呢?

研究

結果透過 SSMS 來看資料庫，發現 SQL 2012 的那個資料庫，相容性層級設定的是 SQL 2005，而 SQL 2016 最底的相容性層級只到 SQL 2008。所以才在 SQL 2016 上才發生這樣子的錯誤。

即然知道問題了，就要看怎麼解了。
依官網來說，程式就給它改下去吧，可能要盤點一下有那些 Store Procedure 有影響吧。

另一個解法是設定 ANSI_PADDING 為 OFF (預設為 ON)。
如果目前使用 Replace 是因為遇到了 Char 資料型態，可以在運算之前，設定 ANSI_PADDING 為 OFF，這樣子就不會把空字串加上去了。
例如，

CREATE TABLE [dbo].[table1] (
    [id]        TINYINT       IDENTITY (1, 1) NOT NULL,
    [char1]     CHAR (10)     NOT NULL,
    [nchar2]    NCHAR (10)    NOT NULL,
    [varchar1]  VARCHAR (10)  NOT NULL,
    [nvarchar2] NVARCHAR (10) NOT NULL
);

truncate table table1;

insert into [dbo].[table1]([char1], [nchar2], [varchar1], [nvarchar2])
values ('char1', N'nchar2', 'varchar1', N'nvarchar2');

-- 預設是 SET ANSI_PADDING ON
-- 所以 char1 欄位 replace 後，會留有空白哦!
SET ANSI_PADDING ON;
select '[' + char1 + ']' as char1,
'[' + replace(char1, 'char', 'rm') + ']' as char1_replace ,
'[' + nchar2 + ']' as nchar2,
N'[' + replace(nchar2, N'char', N'rm') + N']' as nchar2_replace,
'[' + varchar1 + ']' as varchar1,
'[' + replace(varchar1, 'char', 'rm') + ']' as varchar1_replace,
'[' + nvarchar2 + ']' as nvarchar2,
N'[' + replace(nvarchar2, N'char', N'rm') + N']' as nvarchar2_replace
from table1;

SET ANSI_PADDING off;
-- 所以 char1 欄位 replace 後，會不會有空白了哦~
select '[' + char1 + ']' as char1,
'[' + replace(char1, 'char', 'rm') + ']' as char1_replace ,
'[' + nchar2 + ']' as nchar2,
N'[' + replace(nchar2, N'char', N'rm') + N']' as nchar2_replace,
'[' + varchar1 + ']' as varchar1,
'[' + replace(varchar1, 'char', 'rm') + ']' as varchar1_replace,
'[' + nvarchar2 + ']' as nvarchar2,
N'[' + replace(nvarchar2, N'char', N'rm') + N']' as nvarchar2_replace
from table1;

執行結果如下，

可以看到上面 ANSI_PADDING ON 時，Replace 後是有空白的，Off 之後 char1 欄位是沒有空白的。
但對於 NCHAR 的資料型態是沒有改變的哦!

所以，如果您在 Replace 遇到的問題是 CHAR ，那您可以在執行 SQL 之前，先去下

SET ANSI_PADDING OFF
-- 你原本的SQL

依據大多數系統大多會將執行 SQL 的部份抽出來，所以目前您可以在那先去設定 ANSI_PADDING OFF。

• 請注意 NCHAR ， ANSI_PADDING 固定會是 ON 的哦! 所以設定 SET ANSI_PADDING OFF 只針對 CHAR 有效果哦!