selecting part of the data in a field

[switchstatement]

i have a field with a full street address, something like "1234 main st." is it possible to select just the numerical part of the data?

i ask because i need to compare that to another field. so it'd be real easy to just do something like

select * from `table` where (MAGIC, `address`) = `other_field`