Changing field types in queries.

Namadoor

Is there a way to change a field's type in a query? What I want to do is order a SELECT query by one field that is a large text, however I want it to behave and sort as though it was an integer. How would I go about doing this?

As always, thanks for any help.

Roger_Ramjet

One way is to use the cast functions .

Namadoor

Okay, I am not quite sure how to use these... I changed

ORDER BY field1, field2 DESC

to...

ORDER BY CAST(filed1 AS INT), CAST(field2 AS INT) DESC

but it didn't work, I also tried replace INT with SINGED and INTERGER. It kept giving me the error:

MySQL Error: You have an error in your SQL syntax near '(field1 AS INT), CAST(field2 AS INT) DESC' at line 5

What am I do doing wrong?

Roger_Ramjet

Check your version of MySQL. As the manual tells you, it needs to be ver 4.0.6 or above to use CAST().
I don't think you can use Cast in the order by clause, should be in the select. Use an aliase for the field, cast it to the type and then order by the aliased field.

SELECT field, cast(field as signed) AS f1 ORDER BY f1

Namadoor

Ah yes, that would be the problem. I have the MySQL packaged with Fedora Core 2, which is version 3.something. Do you know of any other ways to do this that will work on version 3.x and later?

Thanks.