Address Decomposition - 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 2000 Forums

Transact-SQL (2000)

Address Decomposition

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-11 : 14:03:52

I have a Table (TableA) with address column (FAddress) of the form :

Faddress
----------
115 xzxxzx,shhshs,SC,01235
1578 ASGGAGA,afffaggagaa,OH,44505
24 sgsjjsjj-hhshs skj,affahhahhayta,AL,15231

I will like to decompose the address column into four (4) columns as follows:

Steet City State Zip

115 xxzx shhshs SC 01235
1578 ASGGAGA afffaggagaa OH 44505
24 sgsjjsjj-hhshs skj affahhahhayta AL 15231

Any help will be most welcomed.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-11 : 16:28:21

I am sure there are better ways, but this works. Relies on a 5 digit zip in every record as well as a 2 digit State, or some more substring/charindex type functions would have to be there.


Declare @foo varchar(150)

Set @foo = '115 xzxxzx,shhshs,SC,01235'

Select Street = left(@foo,charindex(',',@foo)-1)
       ,City = LEFT(replace(@foo,left(@foo,charindex(',',@foo)),''),charindex(',',replace(@foo,left(@foo,charindex(',',@foo)),''))-1)
       ,State = substring(@foo,len(@foo)-7,2)
	,ZIP = right(@foo,5)

returns:
115 xzxxzx shhshs SC 01235

24 sgsjjsjj-hhshs skj affahhahhayta AL 15231

From your samples.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 00:27:11

Try:-
SELECT PARSENAME(REPLACE(FAddress,',','.'), 4) AS Street,
PARSENAME(REPLACE(FAddress,',','.'), 3) AS City,
PARSENAME(REPLACE(FAddress,',','.'), 2) AS State,
PARSENAME(REPLACE(FAddress,',','.'), 1) AS Zip
FROM TableA

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-16 : 10:09:59

Thanks a million to visakh16 and dataguru1971
your solutions rocks !!!!!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-17 : 00:58:47

As long as there are four values in address column, use of parsename is better in this case as no need to rely on the length of data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources