HOW EASILY SPLIT ADDRESSE

[powerprinting]

HERE IS AN EXAMPLE OF THE ADDRESSES IN A FILE I AM WORKING ON............................................

28829ORCHARD LAKE RD
25717POWER RD
28588NORTHWESTERN HWY
315MELFORD
30560BECK RD
..............................................
What command or snippet is most useful for seperation of the number and road portion of the address? Any ideas of a simple useful command I am overlooking?

Thanks

 

[Stella740pl]

Can a number be a part of a street/road name? Say, 134 56th North Road?

If so, how in that case the number would be separated from the street name?

If not, you can just loop through the symbols of your string, extract each with SUBSTR() and check it with ISDIGIT() or ISALPHA(), and build your new strings along the way. While symbols are numeric, add them to your Number string, everything after that add to Road string.

Stella

 

[powerprinting]

Can a number be a part of a street/road name? Say, 134 56th North Road?

I can find no instance of that.. the database is small enough I could catch those later anyway. Still not sure how to write the loop routine though. Although in theory, it looks like what I need to do.

 

[JRB-Bldr]

mnLen = LEN(ALLTRIM(address)
FOR i = 1 TO mnLen
mcTstChr = SUBSTR(address,i,1)
IF !BETWEEN(ASCII(mcTstChr), 48, 59)
* --- Test Character is Not Numeric ---
EXIT
ENDIF
ENDFOR

Depending on what you want to do...

If you want to insert a SPACE between numbers and street:
REPLACE address WITH STUFF(address,i,0," ")

If you want to put parts into separate variables:
mcNumber = LEFT(address,i-1)
mcStreet = SUBSTR(address,i)

To modify the entire table, you might do a SCAN/ENDSCAN and modify each record accordingly.

Good Luck,
JRB-Bldr

 

[jimstarr]

The following solution is simplified as it assumes that the address always has both a numeric and an alpha part.

FOR i = 1 TO LEN(address)
x = SUBSTR(address, i, 1)
IF ISALPHA(x)
EXIT
ENDIF
ENDFOR

numstr = LEFT(address, i - 1)
alphastr = SUBSTR(address, i)

Jim

 

[Stella740pl]

I didn't test this, and I assume that you already have a table MyTbl with a field Address in it, where the source data is coming from, and that you already have a table NewTbl with fields Number and Street, to insert you newly extracted values.

SELECT MyTbl

SCAN
	nStrLen=LEN(ALLTRIM(Address))
	sNumber=''
	sStreet=''
	FOR i=1 TO nStrLen
		sSymb=SUBSTR(Address,i,1)
		IF ISDIGIT(sSymb)
			sNumber=sNumber+sSymb
		ELSE
			sStreet=SUBSTR(Address,i)
			EXIT
		ENDIF
	ENDFOR
	INSERT INTO NewTbl(Number, Street) VALUES (sNumber, sStreet)
ENDSCAN