• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

parsing address using tsql

I have a legacy table with input in the address field like this:

CITY OF ACWORTH TAX DEPA -4415 SENATOR RUSSELL
LUCERNE VILLAGE TAX COL -2073 MAIN RD, STE A
BROCKTON CITY (W/S)- COL 45 SCHOOL STREET
YORK COUNTYTREASURER-28 EAST MARKET ST. -
EASTLAND CAD - TAX COLLE P O BOX 914
BLOOMFIELD VILLAGE TREAS P.O. BOX 609
ABBEVILLE CITY - TAX COL P.O. BOX 1170
ABBEVILLE COUNTY - TREAS P O BOX 38
ABBEVILLE CITY - TREASU P O BOX 40

I need to parse out this field to give me only the address. I am hitting a dead end. Help please!
0
Leogal
Asked:
Leogal
  • 2
  • 2
2 Solutions
 
LeogalAuthor Commented:
Here is the code I am using at this point. It is not totally doing what I want tho...

   , CASE
              WHEN  [PAddress] > ' ' THEN
                           SUBSTRING( [PAddress],charindex( ' - ', ([PAddress]))  , 25)
                              ELSE ' '
               END AS [addr1]


here are the results , close but not exactly right as I only need the address....

CITY OF ACWORTH TAX DEPA
LUCERNE VILLAGE TAX COL
BROCKTON CITY (W/S)- COL
YORK COUNTYTREASURER-28
 - TAX COLLE P O BOX 914
BLOOMFIELD VILLAGE TREAS
 - TAX COL P.O. BOX 1170
 - TREAS P O BOX 38
 - TREASU P O BOX 40
 - COLLE 269 HIGH ST
 - TAX COLL 60 N PARKE ST
 - TREASURE P O BOX 789
0
 
Anthony PerkinsCommented:
Perhaps something like this:
, CASE 
	WHEN LEN(PAddress) > 0 AND CHARINDEX('-', (PAddress)) > 0 THEN SUBSTRING([PAddress], CHARINDEX('-', (PAddress)) + 1, 25)
	ELSE ' '
  END AS [addr1]

Open in new window

0
 
Anthony PerkinsCommented:
This is how I tested it:
DECLARE @YourTable TABLE(
		PAddress varchar(100))

INSERT @YourTable(PAddress)
VALUES  ('CITY OF ACWORTH TAX DEPA -4415 SENATOR RUSSELL'),
	('LUCERNE VILLAGE TAX COL -2073 MAIN RD, STE A'),
	('BROCKTON CITY (W/S)- COL 45 SCHOOL STREET'),
	('YORK COUNTYTREASURER-28 EAST MARKET ST. -'),
	('EASTLAND CAD - TAX COLLE P O BOX 914'),
	('BLOOMFIELD VILLAGE TREAS P.O. BOX 609'),
	('ABBEVILLE CITY - TAX COL P.O. BOX 1170'),
	('ABBEVILLE COUNTY - TREAS P O BOX 38'),
	('ABBEVILLE CITY - TREASU P O BOX 40')

SELECT  CASE 
		WHEN LEN(PAddress) > 0 AND CHARINDEX('-', (PAddress)) > 0 THEN SUBSTRING([PAddress], CHARINDEX('-', (PAddress)) + 1, 25)
		ELSE ' '
        END AS [addr1]
FROM    @YourTable

Open in new window

Here is the output:
4415 SENATOR RUSSELL
2073 MAIN RD, STE A
 COL 45 SCHOOL STREET
28 EAST MARKET ST. -
 TAX COLLE P O BOX 914
 
 TAX COL P.O. BOX 1170
 TREAS P O BOX 38
 TREASU P O BOX 40
0
 
awking00Commented:
>>I have a legacy table with input in the address field like this:
CITY OF ACWORTH TAX DEPA -4415 SENATOR RUSSELL
LUCERNE VILLAGE TAX COL -2073 MAIN RD, STE A
BROCKTON CITY (W/S)- COL 45 SCHOOL STREET
YORK COUNTYTREASURER-28 EAST MARKET ST. -
EASTLAND CAD - TAX COLLE P O BOX 914
BLOOMFIELD VILLAGE TREAS P.O. BOX 609
ABBEVILLE CITY - TAX COL P.O. BOX 1170
ABBEVILLE COUNTY - TREAS P O BOX 38
ABBEVILLE CITY - TREASU P O BOX 40<<
So what do you want to see as the addresses from these?
0
 
LeogalAuthor Commented:
I wanted to be able to parse the address field showing only the street address or post office box.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now