Address split - 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 split

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-04 : 11:48:01

Hi there,

I have some address data which I need to re-format.
Here is an example of an address

66 n00bz0r Avenue, Apt 222, Dun n00b, Co n00belski

now I need to place the above line into a table where the first bit would be placed in column Line1, the second bit in Line2 and so on. There are 5 columns for the address and a postcode field as well.

Now I've done this before and it has worked but I just want to see some different suggestions.

Any suggestions are welcome.

By the way... my current method is as follows.

Create a table called MY_ADDRESS
and place the above address in the fist column

ADDRESS <----- This is where the example address is held
Address1
Address2
Address3
Address4
Address5

UPDATE MY_ADDRESS
SET ADDRESS1 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, ADDRESS1, '')

and the above two update statments is used again to populate Address2 etc..

your method?!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-04 : 12:20:35

When you say "bit" you don't really mean the BIT datatype do you? What does "bit" mean in elven-speak?

There are several great ways to parse CSV strings ... use the site search. Although you never said your Address was comma delimited ... and you've got only 3 commas but you are parsing into 5 buckets ...

You've made several posts just like this ... "I've got a thing that I already have a working solution for, but I'd like to know if any of you blokes can improve on it" ... then when people make suggestions you come back and say ... "Nice try, but there is this requirement or detail that I didn't tell you about in my initial post. I used by elven powers of telepathy to transmit the message but you must have enough experience points to hear it. So thanks for wasting your time, dummy, but your idea won't work" ....

Jay White
{0}

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 06:23:12

quote:

When you say "bit" you don't really mean the BIT datatype do you? What does "bit" mean in elven-speak?

You said it.. if I meant the datatype BIT then I would have said BIT not bit.....D'oh!

LOOK!

66 n00bz0r Avenue, Apt 222, Dun n00b, Co n00belski

so we have

66 n00bz0r Avenue <----- Line1
Apt 222 <----- Line2
Dun n00b <----- Line3
Co n00belski <----- Line4

The address is in field already not in a csv file... if it was in a csv file I wouldn't be asking the question here!

Also, I suggest you take an over dose of some chill-pills... it worked for me in the past so I am sure it will work for you as well.
No need to bite my head off... if you don't like the question... don't answer it.

Kes Umaak Page47.... La! kes Umaak martayan...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-05 : 07:40:37

quote:

66 n00bz0r Avenue, Apt 222, Dun n00b, Co n00belski

so we have

66 n00bz0r Avenue <----- Line1
Apt 222 <----- Line2
Dun n00b <----- Line3
Co n00belski <----- Line4

Which of these is the input and which is the output? I thought you where starting with a comma-sep address and you wanted to parse it into columns in a table....but what are Line{1-4}? Have you been blowing winter up your nose again? And who said anything about a file? I said there are ways to parse CSV strings ... do you use your gun to see at night and sleep with your flashlight?

Try to focus for 2 minutes and clearly explain your environment and the task you are trying to accomplish. Preferably shying away from non-database terms like line...

Damn, dude ... this is fun. This really is how all your posts go isn't it? You post a question and then some fool (me) tears their hair out trying to translate your mish-mush into something that makes sense. I'm game for gittin down wit da git down....

Jay White
{0}

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 08:26:42

**SIGH**

Think of it as follows....

you get some data from an external source.. let's say some people's address details...

Mr Brown4 blah, flat 8, Highfield Road, Surrey, Kent, UK
Mr Green 47 Neton Drive, Framwellgate Moor, London, BN18763

Now the above data is stored in a table already, with the address (all it's parts stored in ONE column).

In order to migrate the data, the addrress part is going into a table called ADDRESS which holds the address in separate parts.
So Line1, Line2, Line3 are actually Address part1, Address part 2 etc etc..

The way I've been doing it is as follows...

Create a table called MY_ADDRESS with the follwing columns...

ADDRESS
Line1
Line2
line3
Line4
Line5

ADDRESS hold the complete address as in

47 Neton Drive, Framwellgate Moor, London, BN18763

but now we need to place 47 Newton Drive in Line1, Framwellgate Moor in Line2 etc...

The way I've been doing it as as follows:

UPDATE MY_ADDRESS
SET LINE1 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, LINE1, '')

Now you can see that after the second update, the first part of the address has been replaced with nothing and so the same process could be repeated against for the rest of the address parts.
Does it make sense now?

Now I know that my method DOES work... but I want to have other methods because I don't think my way is the best one....

Page47 -- There is no harm in wanting different methods AND discussing them.... I know I can solve most SQL problems thrown in my face but I am interetsed more in finding BETTER solution not just solutions! (Unlike the U-SUK forces

)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-05 : 08:46:27

You've got only 3 (and now 5) commas but you are parsing into 5 columns... what defines a "part" and a "line"? If a comma defines a part what goes into Address5 or Line5? How bout you provide a create table statement and some insert statements to put some sample data into that table. Then show how you would like the table to look after the update statement has been executed.

...Or...

Take another puff, cut and paste your initial post ... add some another "sigh" at the top and post it again ... and while your at it, keep up the practise of changing the names of your columns with each post. You and I, Mr. Night Elf, will do our best to keep SQL Team not boring.

Jay White
{0}

Edited by - Page47 on 03/05/2003 08:55:04

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-05 : 09:08:32

Here's a fun way to do it all in 1 SELECT. I didn't find the request too confusing, maybe slightly ambibuous but with a few minor assumptions it made sense.

Enjoy!

(I think he might be implying that there could be UP TO 5 parts of the ~~date~~ oops address that should be separated; probably could be LESS than 5 (or 4 or 3) for some addresses as well... i.e., some address have an extra line with Apt #, some do not)

----------------------------

declare @t table (Address varchar(100));

insert into @t
select 'test1, test2, test3' union
select 'abc, def, ghi, jkl, mno' union
select '1234, 5678' union
select '100 Main Street, Boston, MA 02115'

select Address, substring(a,1,comma1-1) as Line1,
substring(a,comma1+1, comma2 - (comma1 + 1)) as Line2,
substring(a,comma2+1, comma3 - (comma2 +1 )) as Line3,
substring(a,comma3+1, comma4 - (comma3 + 1 )) as Line4,
substring(a,comma4+1, comma5 - (comma4 + 1)) as Line5
FROM
(
select *, Charindex(',',A,Comma4+1) as Comma5
FROM
(
select *, Charindex(',',A,Comma3+1) as Comma4
FROM
(
select *, Charindex(',',A,Comma2+1) as Comma3
FROM
(
select *, Charindex(',',A,Comma1+1) as Comma2
FROM
(
select Address, Address + ',,,,,,' as A, charindex(',',Address + ',') as Comma1
from @t
) A
) B
) C
) D
) E

- Jeff

Edited by - jsmith8858 on 03/05/2003 09:09:19

Edited by - jsmith8858 on 03/05/2003 09:20:10

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 09:53:27

I almost got angry at your last post but then I rememberd where you come from so it made sense.... YES... you are 100% right.. it is ALL mu fault.... I apologise and I hope you forgive oh all powerful...

Here is the complete code that you want....

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[MY_ADDRESS]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[MY_ADDRESS]
GO

CREATE TABLE [DBO].[MY_ADDRESS] (
[ADDRESS] [VARCHAR] (200) NULL,
[LINE1] [VARCHAR] (200) NULL,
[LINE2] [VARCHAR] (200) NULL,
[LINE3] [VARCHAR] (200) NULL,
[LINE4] [VARCHAR] (200) NULL,
[LINE5] [VARCHAR] (200) NULL
) ON [PRIMARY]
GO

INSERT INTO MY_ADDRESS(ADDRESS)
SELECT '22 Cedarwood Green, Glasnevin, Dublin 11'
UNION ALL
SELECT '28 Briarwood, Vevay Road, Bray, Co Wicklow'
UNION ALL
SELECT 'Pembroke, Rathdown Road, Greystones, Co. Wicklow'
UNION ALL
SELECT '7 The Grove Pheasant Run, Littlepace, Clonee, Dublin 15'

UPDATE MY_ADDRESS
SET ADDRESS = ADDRESS+','

UPDATE MY_ADDRESS
SET LINE1 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, LINE1, '')

UPDATE MY_ADDRESS
SET LINE2 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, LINE2, '')

UPDATE MY_ADDRESS
SET LINE3 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, LINE3, '')

UPDATE MY_ADDRESS
SET LINE4 = SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS))

UPDATE MY_ADDRESS
SET ADDRESS = REPLACE(ADDRESS, LINE4, '')

It is a crap way of doing it and I am sure there must be a better way of doing it.... your suggestion Page[Kess Umaak] ?

Thanks in advance.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-05 : 10:02:36

Jeff, this guy still owes you beer ... have you forgotten?


declare @a smallint,@b smallint,@c smallint,@d smallint,@e smallint
update my_address
set
    @a = 1,
    line1 = substring(address,@a,charindex(',',address)-1),
    @b = nullif(charindex(',',address),0),
    line2 = substring(address,@b+1,isnull(nullif(charindex(',',address,@b+1),0)-@b-1,len(address)-@b)),
    @c = nullif(charindex(',',address,@b+1),0),
    line3 = substring(address,@c+1,isnull(nullif(charindex(',',address,@c+1),0)-@c-1,len(address)-@c)),
    @d = nullif(charindex(',',address,@c+1),0),
    line4 = substring(address,@d+1,isnull(nullif(charindex(',',address,@d+1),0)-@d-1,len(address)-@d)),
    @e = nullif(charindex(',',address,@d+1),0),
    line5 = substring(address,@e+1,len(address)-@e)

Is it better? I dunno ... different yeah ... what do you think elf? slightly less boring yet?

Jay White
{0}

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 10:11:28

quote:

Jeff, this guy still owes you beer ... have you forgotten?


declare @a smallint,@b smallint,@c smallint,@d smallint,@e smallint
update my_address
set
    @a = 1,
    line1 = substring(address,@a,charindex(',',address)-1),
    @b = nullif(charindex(',',address),0),
    line2 = substring(address,@b+1,isnull(nullif(charindex(',',address,@b+1),0)-@b-1,len(address)-@b)),
    @c = nullif(charindex(',',address,@b+1),0),
    line3 = substring(address,@c+1,isnull(nullif(charindex(',',address,@c+1),0)-@c-1,len(address)-@c)),
    @d = nullif(charindex(',',address,@c+1),0),
    line4 = substring(address,@d+1,isnull(nullif(charindex(',',address,@d+1),0)-@d-1,len(address)-@d)),
    @e = nullif(charindex(',',address,@d+1),0),
    line5 = substring(address,@e+1,len(address)-@e)

Is it better? I dunno ... different yeah ... what do you think elf? slightly less boring yet?

Jay White
{0}

YESSS... your solution is better... thank you.

see... that's how you learn... can I borrow pafe number 47 from you for a couple of days...

Sriously... cheers for that code... much appreciated.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-05 : 10:17:34

I'd be interested to know why it is better? Did you do some performance testing? I didn't ...

What kind of sample data did you use for you test, what kind of metrics did you caputre and what were the results?

Did you test Jeff's too?

Jay White
{0}

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 10:20:50

quote:

I'd be interested to know why it is better? Did you do some performance testing? I didn't ...

What kind of sample data did you use for you test, what kind of metrics did you caputre and what were the results?

Did you test Jeff's too?

Jay White
{0}

You tell me.....
which is more efficient.... 1 UPDATE or 10 UPDATES?
or do you need to run an execution plan for that?

And... anyway.... Jeff's is cool as well...

Thanks... Page[Kes Umaak]... LOL

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-05 : 10:33:37

quote:

You tell me.....
which is more efficient.... 1 UPDATE or 10 UPDATES?
or do you need to run an execution plan for that?

How did you form that theory?

Jay White
{0}

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 10:48:42

quote:

quote:

You tell me.....
which is more efficient.... 1 UPDATE or 10 UPDATES?
or do you need to run an execution plan for that?

How did you form that theory?

Jay White
{0}

It's called common sense...
based on my example of the address.. it is ceryainly more efficent doing it using your method than running my other updates....

oh... when was the last time you had some {0} BTW?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-05 : 11:07:07

quote:

Jeff, this guy still owes you beer ... have you forgotten?

That's right .. I think it was 100 beers ... with interest I'd say it's up to 105 or so ....

- Jeff

Crespo24
Village Idiot

144 Posts

Posted - 2003-03-05 : 11:21:55

quote:

quote:

Jeff, this guy still owes you beer ... have you forgotten?

That's right .. I think it was 100 beers ... with interest I'd say it's up to 105 or so ....

- Jeff

If you're ever in Paris... come to the 'Frog 'n' Princess' pub and I will get you 105 Blonde Beer.. don't worry.. I know the guy who works there....

Page48 gets a few as well... because I have to say.. even though he annoys the hell out of me... he has a cracking set of brain cells....
so... cheers big ears!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources