CODEWITHSUNDEEP
sqlms-access
Oliver Amundsen
Oliver Amundsen

Reputation: 1511

Remove Alpha numeric from string in MS Access or SQL

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!

Upvotes: 2

Views: 1357

Answers (1)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 2

Related Questions