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