How do I extract only numbers from a field? (Ms Access Sql query)

[si_the_geek]

Mid(Field1, 1, Len(Field1)) isn't much use, at it is just a very long-winded way of writing Field1

Field1 Like '%[0-9]%' would be a very good thing to put in a Where clause for this (as it will quickly eliminate any rows that don't have numbers in them), but in the Select clause won't do much good (it will return True/False [or 0/1] depending on whether there are any numeric characters in the field).


As to how you should approach this, it depends heavily on the kind of values you have got and exactly what you want to return based on those values.

[Christhemist]

Input:

1234 Apple Hill Road.

Output:

1234

[Christhemist]

Some will be like this

Input:

Po Box 1234

Output:

1234