seperating a field

[ferrisj]

I have an address field and I would like to seperate the street numbers into a field away from the streets. Is that possible?

 

[Melagan]

Assuming your addresses are in the form "1111 Main Street", this SQL should serve your purpose.

SELECT a.Address
, Left([a].[address],(InStr([a].[address]," "))) AS myNumeric

FROM tblAddresses AS a;

This SQL returned the following results from the following table:

TABLE:
ID	Address
1	1234 Main Street
2	15 A Street
3	10 E Street
4	1053 First Avenue
5	50551 Access Drive
6	684 Microsoft Way

RESULTS:
Address	                myNumeric
1234 Main Street	1234 
15 A Street	        15 
10 E Street	        10 
1053 First Avenue	1053 
50551 Access Drive	50551 
684 Microsoft Way	684

Again this will only work if your addresses begin with the numeric and have a space between the numeric and directional/street name.


~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

UPDATE tblAddresses AS a SET 
a.StreetNum = Left(a.address,(InStr(a.address," ")))
, a.Street = Mid([a].[address],(InStr([a].[address]," ")+1));

..if you want to actually split the address and insert just the numeric part into it's own field and the rest into it's own field.

~Melagan
______
"It's never too late to become what you might have been.

 

[ferrisj]

how would i display the test portion in another field alongside the numeric field

 

[Melagan]

What do you mean by the "test portion" ?

Assuming by "test portion" you mean just the street name, it would be like this:

SELECT a.Address
, Left([a].[address],(InStr([a].[address]," "))) AS myNumeric
, Mid([a].[address],(InStr([a].[address]," ")+1)) AS myStreetName

FROM tblAddresses AS a;

~Melagan
______
"It's never too late to become what you might have been.

 

[ferrisj]

i did. thanks for help. If i were to take it another step. the address field had the Apartment part on the end of the address.

ie

123 Main St. APT a1

Now I have seperated the 123. Can a query be written that says anything with APT in the field show the APT and all text after it in another field?

 

[Melagan]

This just got a whole lot more complicated - my original myStreetName won't be completely accurate if there are apartment numbers after the street name. I'll need a bit more time to figure out how to correctly handle all of these cases.

Unless, ofcourse, an expert can beat me to the punch =)

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Alrighty man, after much blood sweat and tears, here is what I've come up with. It is not perfect but is definitely a good start. Please note that it will ONLY work on seperating apartment numbers if it has the suffix exactly as "APT".

SELECT a.Address
, Left(a.[address],InStr(a.[address]," ")) AS myNumeric
, Mid(a.[address],InStr(a.[address]," ")+1,Len(a.[Address])-InStr(a.[address], "APT")+InStr(a.[Address], " ")-1) AS myStreet
, Mid(a.[Address],InStr(a.[address],"APT")) AS myApt

FROM tblAddresses AS a

This SQL returned the following results with test table contained herein.

[b]tblAddresses[/b]
ID	Address
1	1234 Main St. APT a1
2	154 G Avenue APT 1B
3	10 AB Street
4	16342 Brawley Avenue APT 203
5	1 John Way APT 1

[b]SQL RESULT:[/b]
Address			      myNumeric	 myStreet     myApt
1234 Main St. APT a1         1234       Main St.    APT a1
154 G Avenue APT 1B          154        G Avenue    APT 1B
10 AB Street                 10         AB Street   #Error
16342 Brawley Avenue APT 203 16342      Brawley Ave APT 203
1 John Way APT 1             1          John        APT 1

As you can see, with all sorts of different entries, it comes close...I'm not sure what else can be done to perfect it though.


~Melagan
______
"It's never too late to become what you might have been.