Cast() Question

[zebe]

Hi,

I am working on a database that has a table of class sections. Within this table there is a field called section code, i.e. the class number. Normally I would have made this field have type int, but I needed to make it a varchar field because there are about 6 sections that have non numerical section codes.

On the original server I was able to order by using the CAST() function on the section_code field:

[code]$sql = "SELECT id, section_code, instructor, ins_email, ins_phone, mentor_1, mentor_2, mentor_1_email, mentor_2_email,
day_time, location, LC_focus FROM uri_101_sections ORDER BY CAST(section_code as unsigned)
"; [/code]

I just recently moved this database to a new server and it no longer functions in the same way, in fact I get the following error:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]You have an error in your SQL syntax near '(section_code as unsigned) ' at line 2[/quote]

Does anyone know why this could be occurring or have an alternate solution for this problem? Thank you so much for any help, I appreciate it!

[fenway]

These are only available on MySQL 4+... could that be the problem?

[zebe]

Thanks! That is indeed the problem...

Anyone know a "hack" around the CAST() version problem? I'm running MySQL 3.23.40

[fenway]

If it's just a string column that's actually storing a numeric value, simply using "section_code+0" should get the job done.