I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql> select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Douglas Sims wrote:
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]
