Query with decimal range on text column

[4midori]

Hello,

I have a column in a table with field type text. However, it contains decimal values like 0.312, 0.445, etc.

I am writing a pretty standard query to get date from my table, but am unsure how I can query decimal ranges given that the field type is text. A sample query:

SELECT * 
FROM myTable 
WHERE myfield(BETWEEN 0.350 AND 0.450)

Do I need to do some kind of math function here?

Please advise, thanks.

[mniessen]

It should work fine if you drop the () from your WHERE clause.

But I have to ask: Why do you store decimal values in a text field?

Michaël

[4midori]

Michael,

Thanks. I'm storing decimal vals in a text field within a CMS that automatically creates these fields. I could change the field type, but I'd prefer not to.

The second AND clause is conditional, so I thought I had to have the parentheses?

SELECT *
FROM myTable
WHERE something = 'blat'
AND something else = 'something'
AND(myfield BETWEEN .450 and .550)
AND bork = 'anotherthing'

[mniessen]

SELECT *
FROM myTable
WHERE something = 'blat'
AND something else = 'something'
AND(myfield BETWEEN .450 and .550)
AND bork = 'anotherthing'

The parentheses do absolutely nothing here.

The second AND clause is conditional

I don't understand what you mean. Could you explain a little more?

Michaël

[4midori]

Gracias, Michaël. Pasa que poniendo más atención a los queries, salió bien.

-Ben

[longneck]

you need to use CAST() to convert the field before you do the comparison:

cast(myfield as decimal) between 0.450 and 0.550

[4midori]

Longneck,

It looks like it works without that. I have decimal values stored in the DB.

Is the CAST function required?

Thx.

[longneck]

in mysql? maybe not. but it would be in any other RDBMS.