You can use CAST or CONVERT to see the data as a numeric type.
If the table is very big and you're going to be querying it
intensely, you might want to create a separate column to store the
numeric data.
mysql> select cast('34' AS decimal);
+-----------------------+
| cast('34' AS decimal) |
+-----------------------+
| 34.00 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select cast('hi' AS decimal);
+-----------------------+
| cast('hi' AS decimal) |
+-----------------------+
| 0.00 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
Douglas Sims
Doug@stripped
On Sep 27, 2006, at 10:24 PM, steve@stripped wrote:
> I am looking for any suggestions to this problem. I have a table
> with a
> varchar field. This field can hold textual or numeric data, but it is
> stored in a varchar field so the database sees it all as text.
>
> I need to be able to search and sort this field as if it were numeric.
> For example, here is some sample data
>
> 2.5
> 4
> 2
> 6
> 7
> 6.2
> 3.4
> 6
>
> I need to be able query the table to get the rows within a certain
> range,
> for example, between 4 and 7:
>
> select * from table where field1>=4 and field1<=7
>
> This doesn't work because the column is not a numeric data type.
> Is there
> anyway to dynamically cast the data to a numeric format so I can use
> MySQL's numeric sorting?
>
> I can't change the field's data type because it also needs to be
> able to
> hold textual data. Thank you for your help.
>
> --
> Steve Musumeche
> CIO, Internet Retail Connection
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
