Parsing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parsing

Author

Topic

Netgame
Starting Member

4 Posts

Posted - 2009-09-15 : 14:37:33

Can someone give a hand with this one?

I have a two address fields, Addr1 and Addr2, that I am trying to parse out into three new fields, City State Zip.

The City State and Zip can be placed in either one of the fields, Addr1 or Addr2. My first step is to write a script that will identify which one of the fields contains the City State and Zip. I think that I can handle that part by having the it look to see which one has a "Space + a two digit State Code".

The second part, which I really need help with, will be to parse it out to the three new fields.

Here are some of the examples of what the data looks like.

Addr1
5555 COLUSA STREET
5555 BUCHANAN ST
USA BOX 555
USA BOX 555
555 PARNASSUS 4TH FL
555 52ND STREET
SAN FRANCISCO, CA 94143
SAN FRANCISCO CA 94143

Addr2
VALLEJO, CA 94590
SAN FRANCISCO CA 94115
SAN FRANCISCO, CA 94143
SAN FRANCISCO CA 94143
SAN FRANCISCO CA 94143
OAKLAND CA 94609
0
0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-16 : 02:34:44

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-16 : 08:04:18

--May not be optimal for Bigger tables as it goes for iterative String manipulation

Create Table Table1(Addr1 varchar(100),Addr2 Varchar(100))

Insert into Table1(Addr1,Addr2) Values('5555 COLUSA STREET','VALLEJO, CA 94590')
Insert into Table1(Addr1,Addr2) Values('5555 BUCHANAN ST','SAN FRANCISCO CA 94115')
Insert into Table1(Addr1,Addr2) Values('USA BOX 555','SAN FRANCISCO, CA 94143')
Insert into Table1(Addr1,Addr2) Values('USA BOX 555','SAN FRANCISCO CA 94143')
Insert into Table1(Addr1,Addr2) Values('555 PARNASSUS 4TH FL','SAN FRANCISCO CA 94143')
Insert into Table1(Addr1,Addr2) Values('555 52ND STREET','OAKLAND CA 94609')
Insert into Table1(Addr1,Addr2) Values('SAN FRANCISCO, CA 94143','0')
Insert into Table1(Addr1,Addr2) Values('SAN FRANCISCO CA 94143','0')

CREATE FUNCTION Get_CityStateZip
(
@Addr1 Varchar(100),
@Addr2 Varchar(100),
@RequiredColumn Varchar(10)
)
RETURNS
VARCHAR(100)
AS
BEGIN

Declare
@SearchAddr varchar(100),
@EOL Bit,
@charPosition int,
@WhiteSpace int,
@strTemp varchar(100),
@StrCity varchar(100),
@StrState char(2),
@StrZip char(5),
@cnt int

Set @SearchAddr=@Addr1
Set @cnt =1
While(@cnt<=2)
Begin
Set @EOL=0
Set @charPosition=1

While(@EOL=0)
Begin
Set @WhiteSpace=charindex(' ',@SearchAddr,@charPosition)
Set @strTemp=substring(@SearchAddr,@charPosition,(CASE WHEN @WhiteSpace > 0 THEN @WhiteSpace-@charPosition ELSE 999 END))
Set @charPosition=@WhiteSpace+1
if @WhiteSpace=0 Set @EOL=1
If LEN(LTRIM(RTRIM(@strTemp)))=2 AND ISNUMERIC(@strTemp)=0 Set @StrState=@strTemp
If LEN(LTRIM(RTRIM(@strTemp)))=5 AND ISNUMERIC(@strTemp)=1 Set @StrZip=@strTemp
End
Select @StrCity=LTRIM(RTRIM(Replace(Replace(@SearchAddr,@StrState,''),@StrZip,'')))
If Len(@StrState) > 0 AND Len(@StrZip) > 0
Begin
Break
End
Else
Begin
Set @SearchAddr=@Addr2
Set @cnt =@cnt+1
End
End

SELECT @strTemp=CASE @RequiredColumn
WHEN 'state'THEN @StrState
WHEN 'city' THEN @StrCity
WHEN 'zip' THEN @StrZip
ELSE NULL END
RETURN @strTemp
END

SELECT
dbo.Get_CityStateZip(Addr1,Addr2,'State') AS State,
dbo.Get_CityStateZip(Addr1,Addr2,'City') AS City,
dbo.Get_CityStateZip(Addr1,Addr2,'Zip') AS Zip
From
Table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:06:09

quote:
Originally posted by Netgame

Can someone give a hand with this one?

I have a two address fields, Addr1 and Addr2, that I am trying to parse out into three new fields, City State Zip.

The City State and Zip can be placed in either one of the fields, Addr1 or Addr2. My first step is to write a script that will identify which one of the fields contains the City State and Zip. I think that I can handle that part by having the it look to see which one has a "Space + a two digit State Code".

The second part, which I really need help with, will be to parse it out to the three new fields.

Here are some of the examples of what the data looks like.

Addr1
5555 COLUSA STREET
5555 BUCHANAN ST
USA BOX 555
USA BOX 555
555 PARNASSUS 4TH FL
555 52ND STREET
SAN FRANCISCO, CA 94143
SAN FRANCISCO CA 94143

Addr2
VALLEJO, CA 94590
SAN FRANCISCO CA 94115
SAN FRANCISCO, CA 94143
SAN FRANCISCO CA 94143
SAN FRANCISCO CA 94143
OAKLAND CA 94609
0
0

how will you identify statecode? do u have a table with all state codes stored?

ddamico
Yak Posting Veteran

76 Posts

Posted - 2009-09-16 : 13:32:20

DECLARE @StateAbbrev TABLE (
[State] VARCHAR(20),
[abb] CHAR(2)
)
INSERT INTO @StateAbbrev
SELECT 'Alabama', 'AL' UNION
SELECT 'Alaska', 'AK' UNION
SELECT 'Arizona', 'AZ' UNION
SELECT 'Arkansas', 'AR' UNION
SELECT 'California', 'CA' UNION
SELECT 'Colorado', 'CO' UNION
SELECT 'Connecticut', 'CT' UNION
SELECT 'Delaware', 'DE' UNION
SELECT 'Florida', 'FL' UNION
SELECT 'Georgia', 'GA' UNION
SELECT 'Hawaii', 'HI' UNION
SELECT 'Idaho', 'ID' UNION
SELECT 'Illinois', 'IL' UNION
SELECT 'Indiana', 'IN' UNION
SELECT 'Iowa', 'IA' UNION
SELECT 'Kansas', 'KS' UNION
SELECT 'Kentucky', 'KY' UNION
SELECT 'Louisiana', 'LA' UNION
SELECT 'Maine', 'ME' UNION
SELECT 'Maryland', 'MD' UNION
SELECT 'Massachusetts', 'MA' UNION
SELECT 'Michigan', 'MI' UNION
SELECT 'Minnesota', 'MN' UNION
SELECT 'Mississippi', 'MS' UNION
SELECT 'Missouri', 'MO' UNION
SELECT 'Montana', 'MT' UNION
SELECT 'Nebraska', 'NE' UNION
SELECT 'Nevada', 'NV' UNION
SELECT 'New Hampshire', 'NH' UNION
SELECT 'New Jersey', 'NJ' UNION
SELECT 'New Mexico', 'NM' UNION
SELECT 'New York', 'NY' UNION
SELECT 'North Carolina', 'NC' UNION
SELECT 'North Dakota', 'ND' UNION
SELECT 'Ohio', 'OH' UNION
SELECT 'Oklahoma', 'OK' UNION
SELECT 'Oregon', 'OR' UNION
SELECT 'Pennsylvania', 'PA' UNION
SELECT 'Rhode Island', 'RI' UNION
SELECT 'South Carolina', 'SC' UNION
SELECT 'South Dakota', 'SD' UNION
SELECT 'Tennessee', 'TN' UNION
SELECT 'Texas', 'TX' UNION
SELECT 'Utah', 'UT' UNION
SELECT 'Vermont', 'VT' UNION
SELECT 'Virginia', 'VA' UNION
SELECT 'Washington', 'WA' UNION
SELECT 'West Virginia', 'WV' UNION
SELECT 'Wisconsin', 'WI' UNION
SELECT 'Wyoming', 'WY'

DECLARE @Address TABLE (
[id] INT IDENTITY(1,1),
[addr1] VARCHAR(255),
[addr2] VARCHAR(255),
[City] VARCHAR(25),
[State] CHAR(2),
[Zip] VARCHAR(10)
)

INSERT INTO @Address ( [addr1], [addr2] )
SELECT '5555 COLUSA STREET', 'VALLEJO, CA 94590' UNION
SELECT '5555 BUCHANAN ST', 'SAN FRANCISCO CA 94115' UNION
SELECT 'USA BOX 555', 'SAN FRANCISCO, CA 94143' UNION
SELECT 'USA BOX 555', 'SAN FRANCISCO CA 94143' UNION
SELECT '555 PARNASSUS 4TH FL', 'SAN FRANCISCO CA 94143' UNION
SELECT '555 52ND STREET', 'OAKLAND CA 94609' UNION
SELECT 'SAN FRANCISCO, CA 94143', '0' UNION
SELECT 'SAN FRANCISCO CA 94143', '0'

UPDATE Y
SET Y.[City] = Z.[City]
, Y.[State] = Z.[State]
, Y.[Zip] = Z.[Zip]
, Y.[addr1] = CASE WHEN Z.[addrColumn] = 1 THEN '' ELSE Y.[addr1] END
, Y.[addr2] = CASE WHEN Z.[addrColumn] = 2 THEN '' ELSE Y.[addr2] END
FROM @Address Y
INNER JOIN (
SELECT A.[id]
, City = LTRIM(RTRIM(SUBSTRING(A.[addr1], 1, CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) ,A.[addr1])-1)))
, [State] = B.[abb]
, Zip = SUBSTRING(A.[addr1], CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) , A.[addr1])+4, LEN(A.[addr1]) - CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) , A.[addr1])-1)
, AddrColumn = 1
FROM @Address A
INNER JOIN @StateAbbrev B
ON A.[addr1] LIKE '% ' + B.[abb] + ' %'
UNION
SELECT A.[id]
, City = LTRIM(RTRIM(SUBSTRING(A.[addr2], 1, CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) ,A.[addr2])-1)))
, [State] = B.[abb]
, Zip = SUBSTRING(A.[addr2], CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) , A.[addr2])+4, LEN(A.[addr2]) - CHARINDEX(SPACE(1) + B.[abb] + SPACE(1) , A.[addr2])-1)
, AddrColumn = 2
FROM @Address A
INNER JOIN @StateAbbrev B
ON A.[addr2] LIKE '% ' + B.[abb] + ' %' ) Z
ON Y.[id] = Z.[id]

SELECT * FROM @Address

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources