Split Column Into multiple rows.

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

Split Column Into multiple rows.

Author

Topic

anilvaghela
Starting Member

4 Posts

Posted - 2008-11-12 : 07:30:45

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 : 07:34:20

search in forums for parsevalues function

anilvaghela
Starting Member

4 Posts

Posted - 2008-11-12 : 07:46:42

I have been looking for parsevalues function but cannot find anything that matches what i require......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 08:49:03

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

anilvaghela
Starting Member

4 Posts

Posted - 2008-11-12 : 11:38:11

Can someone please help me write some of this up for me....I am not an advanced user in SQL Query and really would appreciate it to heart if someone can give me some code in relation to my original question.

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-13 : 03:44:27

CREATE TABLE #fieldvalue (field VARCHAR(512))

DECLARE @str VARCHAR(512),@str1 VARCHAR(512)

SELECT @str = '"-" "-" "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"'

SELECT @str1 = @str

WHILE(LEN(@str1)<> 0)
BEGIN

INSERT INTO #fieldvalue
SELECT SUBSTRING(LTRIM(@str1),CHARINDEX('"',LTRIM(@str1),1),CHARINDEX('"',LTRIM(@str1),2))

SELECT @str1 = SUBSTRING(LTRIM(@str1),CHARINDEX('"',LTRIM(@str1),2)+ 1,LEN(@str1))
END

SELECT * FROM #fieldvalue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources