Changing field type? 2

[rdcss]

If I have a field that is all numerical, but is set up as varchar, will it cause any problems in the already-entered data if I alter it to make that field an int field?

 

[sleipnir214]

Probably not.

Out of curiosity, why are you changing the column type?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[rdcss]

Because I set it as varchar() when it really needs to be int

I'm wanting to be able to search a range... it seems like that would be more feasible with integers than with strings.

Thanks

 

[sleipnir214]

It probably will be unless you've formatted the numeric strings with leading zeros, which make alphabetic and numeric sorting the same.

I always ask in this type of situation because I've seen too many database schema which used numeric types when string types were more appropriate. One classic case was an type-ID field in an billing-by-hours application -- the field was formatted like "7.1", and by the programmer's using a float to store the field, the software ran into all kinds of data precision errors.

My general rule of thumb has been that unless you're planning on performing numeric calculations on the data, it should be usually be stored in a string type.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[rdcss]

In that case, WOULD it be possible to search a range on the string, thus not needing to change the field type?

The field is a price, and I need to be able to search - for example - everything less than 100,000 or anything above 100,000 or everything between 100,000 and 150,000... ?

 

[sleipnir214]

Again, alphabetical and numerical order is only the same when you've left-padded your numbers with zeros to an arbitrary length.

For example, if you have the values:

150
50
75

in a string, you're going to have problems with sorting and thus finding numbers in between numbers.

Had the numbers been formatted as:
150
050
075

Then it wouldn't matter.

If these numbers will be used to impose a numerical order on the rows, I recommend you go ahead and convert the column to an integer type.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[rdcss]

It isn't for numerical order, it's to find certain groupings.

The customer is a realtor and she wants to be able to find a house by sold price, even if she can't remember the exact price.

So, for example, anything between 100,000 and 150,000 ... or between 75,000 and 100,000

Would this work without converting the field type?

 

[sleipnir214]

If you are searching for values that arithmetically fall between two given values, then you are numerically ordering the data. "Between", "greather than" and "less than" only have meaning in the context of an implicit ordering.

This falls into "performing numerical operations" in my general rule.



Again, the only way you can leave the value as a string and still do meaningful searching is that you have formatted all these numerical strings to a fixed length with left-padding of zeroes.

Otherwise, when you perform the search, "8000" will be found to be between "75000" and "90000".

So you must either left-zero-pad all these values to a fixed length or convert them to numbers. I recommend the latter.


Want the best answers? Ask the best questions!

TANSTAAFL!!