sorting address - 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

General SQL Server Forums

New to SQL Server Programming

sorting address

Author

Topic

paxmaster
Starting Member

4 Posts

Posted - 2009-01-16 : 14:49:41

I have two tables one table (developerteam1) has only two columns FullName and FullAddress and Other table (developerteamexpanded) has FirstName LastName AddressLine1 AddressLine2 City State Zip

I was creating a function but only add1 and add2 works other like City State and zip don't work; hear is the function

alter function Address(@fulladdress varchar(100),@section varchar(35))

returns varchar(100)
as

begin

declare @desiredadd varchar (100)
declare @lenght int
declare @i int
declare @comma1 int
declare @comma2 int
declare @comma3 int
declare @comma4 int

--set @desiredadd = @fulladdress
set @i = 1
set @lenght = len(@fulladdress)

--while (@i <= @length)
--begin
if @section ='add1' begin
set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,1,@comma1-1)
end

else if @section ='add2' begin

set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,@comma1+1,len(@fulladdress))
set @comma2 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@desiredadd,1,@comma2-1)
end

else if @section ='city' begin

set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,@comma1+1,len(@fulladdress))
set @comma2 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@fulladdress,1,@comma2-1)
set @comma3 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@desiredadd,1,@comma3-1)
end

else if @section ='state' begin

set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,@comma1+1,len(@fulladdress))
set @comma2 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@fulladdress,1,@comma2-1)
set @comma3 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@fulladdress,1,@comma3-1)
set @comma4 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@desiredadd,1,@comma4-1)
end

return @desiredadd
end
----
then on I have to create a store proc and use this function to insert values into other table (developerteamexpanded)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-16 : 15:32:50

You didn't say why your function is not working. Have you searched this site for similar topics? (ie: "split address", "city state zip", etc...)

One challenge will be when one or more address sections are missing from the full address. You'll need to define some rules for those possibilities. Are there guaranteed delimiters between the sections? That will make it much easier. Post a good representation of the possible values.

Similar issue with full name, There can be prefixes, suffixes, middle names, middle initials, multi part names, etc.

Be One with the Optimizer
TG

paxmaster
Starting Member

4 Posts

Posted - 2009-01-16 : 16:07:49

yeah I figure out the FullName Columns But I am concern with FullAddress
I have tested the add1 and add2 with this script

select dbo.address(fulladdress, 'add1')
from DeveloperTeam1

select dbo.address(fulladdress, 'add2')
from DeveloperTeam1
it works
but when I try to do City it gives blanks or null

yeah I search the web but no luck that what i am looking for

let me you an example of a row on developerteam1

INSERT INTO DeveloperTeam1 VALUES (
'Albert Butt', '122 Street, Apt No 65, Jersey City, NJ, 07406'
As you see there is comma when there is a comma will be a columns

Thanks for quick reply TG

Paxmaster

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-16 : 16:24:14

Don't "search the web" !
Search THIS site

http://www.sqlteam.com/forums/search.asp

If you can count on comma delimiters then any of the "split functions" on this site will help you break the string.

ie: Best Split Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Be One with the Optimizer
TG

paxmaster
Starting Member

4 Posts

Posted - 2009-01-17 : 02:25:24

I have already had look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
it does not help because I am doing total different thing

I have to insert the value of split form table 1 to table 2 by useing store sproc

select * from dbo.Split(N'paxmaster,TG,Zack',

it will give me:

paxmaster
TG
zack

That's not what i want

The Whole columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 02:48:35

wont this be enough?


INSERT INTO developerteamexpanded
SELECT FirstName,
LastName,
Add1,
Add2,
City,
State,
Zip
FROM
(
SELECT d1.FullName,
MAX(CASE WHEN n.ID=1 THEN n.Val ELSE NULL END) AS FirstName,
MAX(CASE WHEN n.ID=2 THEN n.Val ELSE NULL END) AS LastName
FROM developerteam1 d1
CROSS APPLY dbo.ParseValues(d1.FullName,',')n
GROUP BY d1.FullName
)name
INNER JOIN
(
SELECT d2.FullName,
MAX(CASE WHEN a.ID=1 THEN a.Val ELSE NULL END) AS Add1,
MAX(CASE WHEN a.ID=2 THEN a.Val ELSE NULL END) AS Add2,
MAX(CASE WHEN a.ID=3 THEN a.Val ELSE NULL END) AS City,
MAX(CASE WHEN a.ID=4 THEN a.Val ELSE NULL END) AS State,
MAX(CASE WHEN a.ID=5 THEN a.Val ELSE NULL END) AS Zip
FROM developerteam1 d2
CROSS APPLY dbo.ParseValues(d1.FullAddress,',')a
GROUP BY d2.FullName
)address
ON address.FullName=name.FullName

ParseValues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-17 : 08:38:32

quote:
Originally posted by paxmaster

I have already had look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
it does not help because I am doing total different thing

I have to insert the value of split form table 1 to table 2 by useing store sproc

select * from dbo.Split(N'paxmaster,TG,Zack',

it will give me:

paxmaster
TG
zack

That's not what i want

The Whole columns

I was not attempting to provide you with a complete solution. You seemed to be having a problem simply seperating out a comma seperated list of values. These functions are proven methods to do just that. Once you have them parsed I assumed you had the ability/imagination to complete your specific task(s) on your own. If I over estimated you, I appologize.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources