SQL Server adds empty spaces to value (char) when trying to set null

Question

I am pulling data from SQL Server to my c# project. I have some textboxes that update from page-to-page. I have one textbox in particular that is set up to only accept two characters, which is setup in the database as char(2). If I were to delete those two characters and click my button to update the database and go to the next page, it stores two empty spaces. I need it to just be empty with no spaces. In my other textboxes, this issue does not occur. The database allows the data to be null. I am able to manually enter "null" in the database, but I need it to be done when erasing the two chars and updating it.

Answer 1

A column declared as CHAR(2) may contain one of the following:

• 2 characters
• NULL.

A column declared as CHAR(2) may not contain any of the following:

• 0 characters
• 1 character
• 3 characters
• etc.

When you try to store anything other than either 2 characters or NULL in the column, your database will troll you in the name of some ill-conceived notion of convenience: instead of generating an error, it will store something other than what you gave it to store.

^{(Amusingly enough, receiving an error when doing something wrong is, and historically has been, regarded as an inconvenience by a surprisingly large portion of programmers. But that's okay, that's how we get stackoverflow questions to answer.)}

Specifically, your database will pad the value you are storing with spaces, to match the length of the column. So, if you try to store just one character, it will add one space. If you try to store zero characters, it will add two spaces.

Possible Solutions:

• If you have the freedom to change the type of the column:

  Declare it as VARCHAR instead of CHAR(2), so that it will contain exactly what you store in it.

• If you do not have the freedom to change the type of the column:

  You have to always be manually checking whether you are about to store an empty string into it, and if so, store NULL instead.

Note about Oracle

The Oracle RDBMS before version 11g (and perhaps also in more recent versions, I am not sure, if someone knows, please leave a comment) will do that last conversion for you: if you try to store an empty string, it will store NULL instead. This is extremely treacherous due to the following reasons:

• It is yet one more example of the database system trolling you by storing something very different from what you gave it to store.

• They apply the same rule to all types of character columns, even VARCHAR, which means that you cannot have an empty string even in columns that could accommodate one; you store either NULL or an empty string, you always get NULL back.

• This behavior is completely different from the behavior of any other RDBMS.

• The behavior is fixed, there is no way to configure Oracle to quit doing that.