List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 30 2002 2:18am
Subject:Re: what "Type" of field should a column be that stores numbers...
View as plain text  
At 17:41 -0800 1/29/02, Chadwick Meyer wrote:
>I have a column that stores numbers, i.e. "4,000" "135" "6";
>What should I set the Column Type as, in order to sort properly by that
>field.
>
>the problem: if it's "TEXT", it sorts according to first number, thus the
>above numbers would be:
>"135" "4,000" "6"
>
>this is not acceptable.
>
>But if I set the field to "INT" then I can't store the number 1,000 it won't
>allow the comma, which I really want. Is there a field type I can choose
>that does this? Or do I just have to reformat every number that I retrieve
>and add in commas every three digits?
>
>please tell me I don't.

I suspect that you do.

Sometimes you can store "sort of" numeric values as strings and then
sort them numerically by forcing a string-to-number conversion in the
ORDER BY clause.  (Such as when you bust apart dotted-quad IP numbers
into pieces and sort the pieces numerically.)  To do this, use
ORDER BY str_val+0.  But in your case, that won't work due to the internal
commas.  My guess is that you'll have to reformat.

>thanks in advance,
>chadwick
>
>********************************
>Chadwick Meyer
>816 46th Ave - San Francisco CA 94121
>415.876.0980
>www.chadwickmeyer.com
>********************************

Thread
what "Type" of field should a column be that stores numbers...Chadwick Meyer30 Jan
  • Re: what "Type" of field should a column be that stores numbers...Paul DuBois30 Jan