Sql equilivant to Val() Access function??

[gvcooper]

Does anyone know of a function to return the numeric value of a string that contains some numeric and some non numeric chars. I need to replace some Val() Access functions with a sql equilivant.

e.g. return 123 from the statement: SELECT Val(123mls)
return 4.56 from the statement: SELECT Val(4.56tonnes)

Thanks
George