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
[EMAIL PROTECTED]
On Sep 27, 2006, at 10:24 PM, [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
