Is it Possible to Extract the Numeric part of an Address Field?

[jbright00]

I have a char field which contains addresses and I need to extract just the numeric portion and store it in another field. Is this possible?

Any help would be greatly appreciated! Thank You.

 

[gmmastros]

Are these addresses in the U.S.? addresses in other countries appear significantly different. If the numeric portion you are talking about is the house number, and that house number appears first, then the following select statement should work pretty well. There is a problem that the following select statement will not accomodate. I've seen addresses with 1/2 numbers, like 121 1/2 Main St.

select 	Address1, 
	Case IsNumeric(Left(Address1, CharIndex(' ', Address1)))
		When 1
		Then Left(Address1, CharIndex(' ', Address1))
		Else NULL
		End As HouseNumber
from 	Address

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbright00]

The numeric part may or may not be first. It could be for example, PO Box 1234. Is it possible to capture the 1234 part of such a string?

Thanks!

 

[Elegabalus]

Parsing addresses is extremely difficult, and requires a great deal of thought as to the patterns you'll see.

You need code that will recognize the difference between:
Apt. 101 1st Street
#101 - 1st Street
1st Street, Apt. 101, etc.

The best bet is to get your addresses in a standard format, then use the regular SQL commands to parse them...otherwise, there are simply too many permutations to do it very easily.

To get the addresses in a good format, I'd recommend using regular expressions to separate out the addresses...not sure if you can use them through SQL Server or not, but that would be my recommendation...

 

[jbright00]

Well, I just need the numeric part. So, if it was Apt. 101 1st Street, I need '1011'. If it is 1st Street, Apt. 101 then I just need '1101'. I am extracting out an "address key" for a lookup system, so this is why the first 4 numeric characters is all I need.

Thanks!

 

[payback]

What is your interface?

If using VB/VBA you can loop through each character in the line and test if numeric - if so, append it to a variable that you save in a field.

 

[gmmastros]

I found this post mildly challenging, so I will post my solution. It is inherently slow, but if you only need to use this to create the lookup key, then performance should be much of an issue.

To accomplish this, I loop through ascii codes (ignoring those for numbers) and replace the character value with nothing. The code you are about to look at uses a temp table. I fill up the temp table from an address table in one of my databases, so you will have to change table names and field names accordingly. This procedure does not update any data anywhere (except in the temp table).

It's always a good idea to select data before updating anyway. Hoep this helps

Declare @i Integer
Create Table #Address (Address1 VarChar(50), Numbers VarChar(50))

Insert
Into	#Address(Address1, Numbers)
Select	Address1, Address1
From	Address

Set @i = 0

While @i < 256
  Begin
    If Not @i Between 48 And 57
      Update #Address Set Numbers = Replace(Numbers, Char(@i), '')
  
  Set @i = @i + 1
  End

Select * From #Address

Drop Table #Address

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbright00]

Thanks a lot George. I think that's exactly what I was looking for.

I have a Delphi app that imports a 600+ MB text file into 2 Access databases. I am replacing it with a SQL Server DTS Package. The current process (with the app) is taking 24+ hours to run... so I am certain that even with this loop it will still be many times faster.

Thanks again!

 

[jbright00]

Also, thanks to everyone else who gave input. Your ideas were helpful as well.