List:General Discussion« Previous MessageNext Message »
From:Steve Musumeche Date:September 28 2006 6:57pm
Subject:Re: making varchar field to act like numeric field
View as plain text  
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
steve@stripped



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
> 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