How to query first word contains? Or how to query first word begins with AND ends with?

[New2Acess]

I have a file with about 1 million addresses. I am trying to filter for addresses that do not contain street numbers. I am easily able to group the streets that are words (ex. cherry street) by sorting the address column a-z but I am having a much harder time with numbered streets (ex. 13th street). I can filter these streets one by one using 'begins with' but that would take forever. I would like to be able to find a query that would let me filter for first word begins with a number (1-0) and ends with 'st' or 'th'. That would give me all of the address I am looking for at once.



Any other suggestions are welcome

[aytee111]

Like "?*st * OR Like "?*th *"

This might narrow it down, but you could add other combinations too.

Include the space so that it doesn't appear in words.

[rpeare]

If you are able to extract the street name (cherry, 1st, 22nd) from the address reliably you can place a criteria on the field testing the first and last character of the street name for isnumeric and not isnumeric respectively, just be aware that something like 12th and 12 th will not return the values you want. These types of strings are notoriously difficult to handle.

If you are going to use aytee's method remember to include *nd

[New2Acess]

Thanks guys

The solution I found was

WHERE address LIKE '#ST*'; and so on. Each Number is represented by one #.

So If I am looking for and 3 digit number ending in rd (ex. 123rd Street or 943rd Street) I would query WHERE address LIKE '###RD*';