I have a bit of trouble with a field that is supposed to represent age. However, the field was setup not as an int, but as a varchar so people entered wierd stuff like '9 1/2', '6 years old' and stuff like that.
I want to be able to put this field in a WHERE clause like 'SELECT * FROM myTable WHERE (age>5 AND <30)'. since this isn't an int field, the rows are innaccurately.
Is there anyway to massage this data to where it needs to be (an integer or real) from a SQL statement/function?
The short answer is that there isn't a short answer.
If you insist on data that you can really rely on, put a birthdate column into the table, declare all rows with a NULL value to be incomplete (unusable), and validate the dates comming in (MySQL is very tolerant of dates that are completely unusable for legal requirements). Then you'll have to get the users to get the missing data in to you.
If you can deal with a swag, then you might be able to write an expression converter that could deal with the data that you've got, but it would probably be a fair challenge to write!