CHAR behavior with trailing spaces.

Question

Given the SQL statements below, I would expect the result of the query to be:

|testX|5|
|XXXXX|5|

This is because the column is a char(5) and I would expect it to insert blank trailing spaces to fill the remaining space in the column since the inserted values are less than five characters. However, the query actually produces the following result:

|test|4|
|X|0|

Can someone please explain this. Why aren't the values padded with trailing blanks to make them fill up the length 5 column? Why does the REPLACE function insert a single X for the second value when it is a completely empty string?

USE Test;
GO

SET ANSI_PADDING ON;
GO

CREATE TABLE BlankTest
(
    Value char(5) NOT NULL
);
GO

INSERT INTO BlankTest (
    Value
) VALUES (
    'test'
);
GO

INSERT INTO BlankTest (
    Value
) VALUES (
    ''
);
GO

SELECT REPLACE(Value, ' ', 'X'), LEN(Value)
FROM BlankTest;
GO

DROP TABLE BlankTest;

Answer 1

REPLACE returns varchar

Try this for the SELECT

...REPLACE(Value, ' ', 'ZZZZZZZZZ')

This can't be char(5), of course, so what is it

...SQL_VARIANT_PROPERTY (REPLACE(Value, ' ', 'ZZZZZZZZZ'), 'MaxLength')...

8000, max of varchar...

Edit:

1. My smart alec colleague reminded me that you'd need to use DATALENGTH to include trailing spaces, not LEN. Asked often here

2. WHERE value = 'test'. 'test' here is varchar which has a higher precedence (fixed) than char, so the column is converted to varchar, thus effectively truncating