List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:September 28 2006 3:48pm
Subject:Re: making varchar field to act like numeric field
View as plain text  
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

Thread
making varchar field to act like numeric fieldsteve28 Sep
  • RE: making varchar field to act like numeric fieldRajesh Mehrotra28 Sep
  • Re: making varchar field to act like numeric fieldDouglas Sims28 Sep
    • Re: making varchar field to act like numeric fieldSteve Musumeche28 Sep
      • Re: making varchar field to act like numeric fieldDan Nelson28 Sep
        • Re: making varchar field to act like numeric fieldSteve Musumeche28 Sep
RE: making varchar field to act like numeric fieldRajesh Mehrotra28 Sep
  • Partitioning to_hourMichael Gargiullo28 Sep
  • Re: making varchar field to act like numeric fieldChris W28 Sep