SQL Replace 後,怎麼會留空白呢? | 亂馬客 | sql移除全形空白
問題最近客戶將SQLServer從SQL2012升級到SQL2016後,有些StoreProcedure居然發生了錯誤。結果發現,原本在SQL2012中去Replace後,原本後面有空字串的會一併RTrim掉,但在SQL2016中,那些空白就會留著。怎麼會這樣子呢?查看官方文件中,這個行為在SQL2008之後就是如此了呀~BehaviorChangestoDatabaseEngineFeaturesinSQLServer2008-REPLACEFunction[1]InSQLServer2005,trailingspacesspecifiedinthefirstinputparametertotheREPLACEfunctionaretrimmedwhentheparameterisoftypechar.Forexample,inthestatementSELECT‘<’+REPLACE(CON...
問題最近客戶將 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[1]
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(C...