Extra spaces in db field (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

carrie -> Extra spaces in db field (6/25/2008 14:11:12)

Hi all,

I am pulling data from a MYSQL db with asp and I've noticed it has extra spaces to the left of the value that I can't seem to trim.
One example is a field marked: Agent ID CHAR 9, but it only has 6 - 7 numbers in the field. So, some fields will have 2 spaces, and some will have three, to the left of the number.
When I use the 'trim' feature like this, it does not remove the spaces:
agentnamestr = trim(ors("Agent ID")).
I don't know why the field has spaces in the first place, but it does. How can I get the spaces removed from the query results?

Carrie



swoosh -> RE: Extra spaces in db field (6/25/2008 15:38:19)

quote:

don't know why the field has spaces in the first place


CHAR 9 would mean 9 characters.

The char is a fixed-length character data type, the varchar is a variable-length character data type.

Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.

You can use char when the data entries in a column are expected to be the same size.
You can use varchar when the data entries in a column are expected to vary considerably in size.




carrie -> RE: Extra spaces in db field (6/25/2008 15:53:53)

Thanks swoosh. I get an index file to load the data into the MySQL db, and that is the way that field is labeled (as well as several others). So I see that if I just change the data type of that field, and reload the data into the MySQL db, then then the spaces will disappear before I run queries.

Excellent!

Thanks again!

Carrie



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625