Holy whitespace batman!

I have an MS Access database that provides various pieces of content for our website. In the past, when I created new tables in the database, I’d send an email out to everyone telling them I was editing the database, grab a copy of the MDB file off the server, make my changes, then copy it back. Tedious to say the least!

So this time I decided to use a query to create the table. I issued the following commands:

create table mytable

and then…

alter mytable
create column columnname char

This gave me my table, and I was able to create all my columns, BUT… for some reason I have tons of whitespace after my data. After doing some research, it seems like the length of each text (char) field is 255 characters, regardless of what data is in there. If I have a 6 letter word, it adds 249 spaces after it.

I ran some queries to trim the whitespace, but it came back. I eventually had to download the database, make a new table and columns the old fashioned way, then run a query to copy the data over from the funky table to the new one while stripping the whitespace out.

What gives?

That would be right - if you specify CHAR, it will give you that many spaces each and every time. It’s perfect for a field where you are GUARANTEED a fixed length field (say SSN). If you have a variable length field, then you want to use VARCHAR.

Aha! That’s exactly what I needed! Thanks Dave!