[SOLVED] convert string to numeric

[rsammy]

i need to convert a string(room number) to numeric before sorting on room_no. how can i do this?

 

this is my code/query:

select pat_dgraphics.pat_first_name, 
pat_dgraphics.pat_mid_init, 
pat_dgraphics.pat_last_name, 
appt_schd.appt_pref_phyID,
DATE_FORMAT(pat_dgraphics.pat_dob, '%m/%d/%Y') as dob, 
pat_dgraphics.pat_ssn, 
appt_schd.appt_loc as admit_visit_loc, 
appt_schd.appt_room_no as admit_room_no,
appt_schd.appt_descr, 
appt_schd.appt_date
FROM appt_schd, pat_dgraphics
WHERE appt_schd.appt_pat_id=pat_dgraphics.pat_ID
AND pat_last_name like '%%'
AND pat_first_name like '%%'
AND (Date_Format(pat_dob, '%m/%d/%Y') like '%%' OR  Date_Format(pat_dob, '%c/%e/%Y') like '%%'  )
AND appt_schd.appt_loc like '%%'
AND pat_dgraphics.pat_client_id ='1'
AND appt_schd.appt_pref_phyID like '%%'
and appt_schd.appt_date ='2007-07-06'
ORDER by admit_room_no ASC

 

now, what this does is, sorts the room number field but, not in the correct order. if the room numbers are like

11, 24, 147, 23, 54 and so on... it sorts them as

11, 147, 23, 24, 54...

 

since room_no is a string, it does this. how do i convert it to a numeric value before sorting it so it gives me an output in the correct order?

[soycharliente]

Why not change the datatype to an INT?

[rsammy]

cant do. cos it can be anyting, Emergency or Room 421-A or something like that. anyways, i guess its not possible to convert it if such values appear. it shud be left as is.

 

thanx for the reply and im closing this thread. sorry!