Split Address in a SQL Table - 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)

Split Address in a SQL Table

Author

Topic

sebaba
Starting Member

4 Posts

Posted - 2008-08-07 : 05:35:33

Hi Guys,

We are writing an integration with a financial system using the SQL 2005 integration service.

I got just one problem we would like to transfer an Address from the financial system. But it is placed in one column with returns in it. So it looks like this:

Postboks 123 Økern 0509 OSLO

I would like to split this up in 4 different columns:

Column1: Postboks 123
Column2: Kern
Column3: 0509
Column4 OSLO

But I cannot find a script to detect the retruns in the orginal address column.

Can somebody help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 05:42:44

use PARSENAME function
SELECT PARSENAME(REPLACE(column,' ','.'),1) AS col1,
REPLACE(PARSENAME(REPLACE(column,' ','.'),2),'Ø','') AS col2,
PARSENAME(REPLACE(column,' ','.'),3) AS col3,
PARSENAME(REPLACE(column,' ','.'),4) AS col4
FROM YourTable

if your values are all consistent containing four parts always

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-07 : 06:03:20

quote:
Originally posted by visakh16

use PARSENAME function
SELECT PARSENAME(REPLACE(column,' ','.'),1) AS col1,
REPLACE(PARSENAME(REPLACE(column,' ','.'),2),'Ø','') AS col2,
PARSENAME(REPLACE(column,' ','.'),3) AS col3,
PARSENAME(REPLACE(column,' ','.'),4) AS col4
FROM YourTable

if your values are all consistent containing four parts always

Note that OP's sample data has five parts, so you may need something like this

SELECT 
	first_address+' '+PARSENAME(REPLACE(col,' ','.'),1) AS col1,
	REPLACE(PARSENAME(REPLACE(col,' ','.'),2),'Ø','') AS col2,
	PARSENAME(REPLACE(col,' ','.'),3) AS col3,
	PARSENAME(REPLACE(col,' ','.'),4) AS col4
FROM 
(
	select substring(col,1,charindex(' ',col)-1) as first_address,substring(col,charindex(' ',col)+1,len(col)) as col from
	(
		select 'Postboks 123 Økern 0509 OSLO' as col 
	) as t
) as YourTable

Madhivanan

Failing to plan is Planning to fail

sebaba
Starting Member

4 Posts

Posted - 2008-08-07 : 06:12:55

Hi Guys,

Thank you very much for the input I will start working on this.

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-07 : 06:18:10

quote:
Originally posted by sebaba

Hi Guys,

Thank you very much for the input I will start working on this.

Regards,

Can you post some more sample data from the table?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 06:32:25

Also have a look at the fnParseString function found here at SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-07 : 06:59:14

Hello @ all,
great solutions but i am missing the notice of the returns mentioned by OP.
Also i think, parsename works reverting.

Webfred

sebaba
Starting Member

4 Posts

Posted - 2008-08-07 : 07:00:40

Hi,

The queries ar working and I got what I want but one last question.

[url]http://cid-5d85f4e1d5bf56f9.skydrive.live.com/self.aspx/Public/Untitled.jpg[/url]

In this screenshot you will see the first row with One return in it so I need to split the values up in col1 and col2.

The other rows are fine they do not have to be split up.

I have tried a where cluase but without any luck.

sebaba
Starting Member

4 Posts

Posted - 2008-08-07 : 07:03:19

Sorry the first row has two retruns so col1, col2 and col3.

anilvaghela
Starting Member

4 Posts

Posted - 2008-11-12 : 12:07:34

Hello!

Ok I have been importing big size logs into ms sql server express 2005 and all the data is in.

This is what the data looks like in each column;

FIELD 1:82.132.138.232
FIELD 2:"28102008-775-82132138232308941225238196"
FIELD 3:"-"
FIELD 4:29/Oct/2008:00:00:00
FIELD 5:""
FIELD 6:"-" "-" "Nokia6300/2.0 (05.50) Profile/MIDP-2.0 Configuration/CLDC-1.1" "-" "-" "-" "NS" "rMNO=(none) aMNO=23410_o2_uk x=(none) hn=news.bbc.co.uk uri=/mobile/bbc_sport/football/teams/b/brighoalb/769/76909/story7690925.wml"

As you can see Field 6 has a lot of data. What I want to do is split Field 6 into 8 Extra Fields using '"' as the seperator.

But I need the splitting to actually make the changes in the same table...which basically updates the table.

Can someone help me with this please......

Much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 12:12:20

quote:
Originally posted by anilvaghela

Hello!

Ok I have been importing big size logs into ms sql server express 2005 and all the data is in.

This is what the data looks like in each column;

FIELD 1:82.132.138.232
FIELD 2:"28102008-775-82132138232308941225238196"
FIELD 3:"-"
FIELD 4:29/Oct/2008:00:00:00
FIELD 5:""
FIELD 6:"-" "-" "Nokia6300/2.0 (05.50) Profile/MIDP-2.0 Configuration/CLDC-1.1" "-" "-" "-" "NS" "rMNO=(none) aMNO=23410_o2_uk x=(none) hn=news.bbc.co.uk uri=/mobile/bbc_sport/football/teams/b/brighoalb/769/76909/story7690925.wml"

As you can see Field 6 has a lot of data. What I want to do is split Field 6 into 8 Extra Fields using '"' as the seperator.

But I need the splitting to actually make the changes in the same table...which basically updates the table.

Can someone help me with this please......

Much appreciated!

Anil dont reopen old threads. you've already posted your question and its been answered. Please dont cross post.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources