Separate Street Numbers from Street Names

[miket48]

Does Alpha Five have an anyalpha() function or equivalent? I am separating street numbers from street names> Typical data would be

10 Ainger Court
1547 Cypress Street
112 Aimes Circle

I need a string function that can find the first alpha character and separate into two fields "street number" and "street name" from field called "address".

[Doug Page]

Re: Separate Street Numbers from Street Names

Look up the WORD() function and see if that will work for you.

[Stan Mathews]

Re: Separate Street Numbers from Street Names

Since you don't have fields "street number" and "street name" let's assume you mean "street_number" and "street_name".

Create an update operation

field                   expression                                                     condition
street_number           word(address,1," ")                                            isdigit(left(address,1))
street_name             alltrim(address)                                               .not. isdigit(left(address,1))
street_name             word_subtract(address,word(address,1," ") )                    isdigit(left(address,1))

[G Gabriel]

Re: Separate Street Numbers from Street Names

I need a string function that can find the first alpha character and separate into two fields "street number" and "street name" from field called "address".

You don't want that!
Even though it is not hard to do, but I don't think you want to do that especially if you live in New York City or Calgary or one of those cities that have so many numbered streets, like:
1234 56th Ave

[miket48]

Re: Separate Street Numbers from Street Names

Thanks everyone. I had been staring at the Word() function for awhile, but hadn't used it before and was unsure how to proceed.

What I ended up doing was to create two new fields in my table, street_number and street_name. I went to edit field rules and made them calculated. For the street_number expression I used WORD(address,1," ") to isolate the first part of the address (before the first space). Then for street_name I used WORD(address,2," ",3) to isolate the second part of the address. That worked to even pick up three word names like "Grand Canyon Court"

Thanks Again,
Mike

[robert]

Re: Separate Street Numbers from Street Names

Somewhere in one of the genies, there's an operation to split fields. I have a table where one of the fields called Records is filled in with text/text. I think I used an update genie to spit the text at the / to fill in two other fields (Record1 and Record2) with the text before and after the /. Below is what A5 shows in the design for this operation:

if(AT("/", RECORD, 1)>0,SUBSTR(RECORD, AT("/", RECORD, 0)+1, (AT("/", RECORD, 1)-AT("/", RECORD, 0))-1),RECORD)

if(AT("/", RECORD, 1)>0,SUBSTR(RECORD, AT("/", RECORD, 1)+1, (AT("/", RECORD, 2)-AT("/", RECORD, 1))-1),"")

I looked at the Xbasic code for this operation but don't really understand it. I could copy it for you.

[G Gabriel]

Re: Separate Street Numbers from Street Names

Then for street_name I used WORD(address,2," ",3) to isolate the second part of the address.

You could and should change the last parameter from 3 to any ridiculous number in case the street name has more than 3 words, especially if you live in Texas.

[robert]

Re: Separate Street Numbers from Street Names

Sorry about my last post. I didn't read close enough to see that the problem had been resolved.

[Doug Page]

Re: Separate Street Numbers from Street Names

This will also work.

street_name = RIGHT(address,LEN(address)-LEN(street_number)-1)