Scenario

As usual when you don’t do something for a very long time, it is easy to forget what you knew already. I had to increase the column size of a CHAR field from 9 to 15 bytes and I totally forgot about some of the gotchas surrounding CHAR fields. There is a reason we mostly use VARCHAR and it is the “VAR” part that we depend on the most.

Gotcha

I increased a CHAR field from 9 to 15 bytes and totally forgot that when you do that a CHAR field will use all of the bytes available. CHAR fields are for fixed width data, not variable width that’s what VARCHAR is for. Therefore any data I had in my table that was 9 bytes wide was now 15 bytes wide because 6 bytes of white space was appended.

Solution

Therefore instead of changing the field from CHAR(9) to CHAR(15), I changed it to VARCHAR(15) and this solved the problem. However there is a caveat to this, if you already changed your field to CHAR(15), those spaces are saved in there, so if you do the following operation:

  • CHAR(9) altered column to CHAR(15)
  • CHAR(15) altered column to VARCHAR(15)

At this point it is too late, you now have contaminated your data with 6 more bytes of white space. The only way for this to be successful is if you go directly from CHAR to VARCHAR.

Proof

I had to exercise a microcosm of the same problem to fully understand what was going on. Here is that code to exercise the same issue described above.

Leave a Reply

Your email address will not be published. Required fields are marked *