here is the code to re-produce the issue:
DECLARE @VBIN BINARY(50)
DECLARE @PASS NVARCHAR(3)
DECLARE @TEXT NVARCHAR(MAX)
SET @TEXT = '123456123789'
SET @PASS = '123'
SET @VBIN = CONVERT(BINARY, N'321')
SELECT REPLACE(@TEXT, @PASS, CONVERT(NVARCHAR(MAX), @VBIN))
would return '321' insetead of '321456321789'
The issue is that binary(50) will pad with 0x00 to reach the fixed length of 50 and that is generally treated as a string terminator.
You see the same behaviour with
SELECT N'The quick brown ' + NCHAR(0) + N' fox jumped over the lazy dog.'
The data is actually still there after the REPLACE. It is in the attempt to display it as a string that truncation occurs.
DECLARE @VBIN BINARY(50)
DECLARE @PASS NVARCHAR(3)
DECLARE @TEXT NVARCHAR(MAX)
SET @TEXT = '123456123789'
SET @PASS = '123'
SET @VBIN = CONVERT(BINARY, N'321')
SELECT REPLACE(@TEXT, @PASS, CONVERT(NVARCHAR(MAX), @VBIN))
SELECT DATALENGTH(REPLACE(@TEXT, @PASS, CONVERT(NVARCHAR(MAX), @VBIN))) /*112*/
SELECT CAST(REPLACE(@TEXT, @PASS, CONVERT(NVARCHAR(MAX), @VBIN)) AS VARBINARY(112))
Using varbinary rather than binary would avoid the issue but I'm not sure what you are actually trying to do here anyway.
