Remove Alpha numeric from string in MS Access or SQL

Question

I have a column with street addresses in MS Access. I need to remove the "st", "nd", "rd", and "th" from the e.g., 21st, 32nd, 33rd, 44th... and keep the numbers... Any ideas how can I do this?

Thanks!

Answer 1

The Val() function could be useful here. It will read digits from a string until it encounters a character which it doesn't recognize as part of a number. See the help topic for more details.

? Val("21st")
 21 
? Val("32nd")
 32 
? Val("33rd")
 33 
? Val("44th")
 44