UPDATE QUERY to Separate Numbers from Text in a Text Field

[pjordan@drcog.org]

So I have a bunch of records such as this in a field:
Foothill Blvd 1450 E
Foothill Blvd 1730 E
Van Buren Ave 37852 W
Haven Ave 7798 N
16th St 280 E


and so on.


I want to somehow split this field to take out only the numbers and following direction (eg. 1450 E) and just leave the street names. Then in another field do the opposite and strip out the Street names and just leave the number and direction.

I tried using a Search/Replace, but realized you can't use # as a wildcard for numbers in a text field (since it takes it as a text # symbol)as in:
FIND WHAT: #*
REPLACE WITH: ""


And tried in an UPDATE QUERY using the Replace function, but the same issue. So just wondering if there was a way to delimit using a wild card the actual numbers and after or is there just no easy way to do this?

[June7]

No Easy Way. Difficult to program. Requires string manipulation functions Left, Mid, Right, InStr, InStrRev.

Every example ends with a number series followed by a space and direction letter. Can that be relied on?

I have never seen street name precede building number.

Bad data schema means complicated data manipulation.

[pjordan@drcog.org]

That's what I thought and yes we get data in lots of formats. We get location in two fields so we get them concatenated and most do well, but there are always a handful that are block data and those come in that style of format so that's why

And yes, this is the problem with the data, we get it in so many different formats and I have to standardize them to get them in a geo-coded format

But thanks for backing up my thoughts on this text data, I figured it's just going to be a manual thing. But good to know...