Unexpected behavior from CONCAT in sql server with some strange, non null value : the value after it are not added to the result

SELECT CONCAT('a', ISNULL(NameStudent, 'ccc'), 'b') as ShouldEndWithb,  NameStudent, LEN(NameStudent) as Length
FROM #Student

There is a strange NameStudent value that gives the result below from this request in ssms. I don't understand why the Concat doesn't end with b and why this seemingly empty value has a length of 2. And after understanding this, I want to know what should I do to have CONCAT behave as expected.

Solution 1:

That's bad data and is exactly what I would expect:

0x00 is the zero-byte character and effectively terminates the string.

You need to fix the source to not send 0x00, because the data is already gone.