Splitting a column into multiple columns

[mossbs]

Hi guys,

I have a table with 1 column in that essentially holds 4 bits of information in it...
e.g.

COLUMN1 
547011 23 MUA 0.2 
547011 24 MUA 0.2 
547011 25 MUA 0.2 
547011 39 MUA 0.2

....what I need to do is to split this out into 4 columns using the spaces as the delimiter...

any ideas how i would go about doing this?

Cheers guys,

Dan

 

[simian336]

There is no built in split function in Sql Server but there are plenty of UDF's out on the web. Just search for SQL SERVER SPLIT FUNCTION and you should get lots of choices.

Simi

 

[simian336]

Most are based on the CHARINDEX fuction btw.

Simi

 

[mossbs]

Cool cheers Simi, I'll have a look about see what i can come up with.

 

[bborissov]

If the data ALWAYS is not more than 4 values:

DECLARE @Test TABLE (Column1 varchar(200))

INSERT INTO @Test VALUES('547011 23 MUA 0.2')
INSERT INTO @Test VALUES('547011 24 MUA 0.2')
INSERT INTO @Test VALUES('547011 25 MUA 0.2')
INSERT INTO @Test VALUES('547011 39 MUA 0.2')

SELECT REPLACE(PARSENAME(REPLACE(REPLACE(Column1,'.','_'), ' ','.'),4),'_','.') AS Test1,
       REPLACE(PARSENAME(REPLACE(REPLACE(Column1,'.','_'), ' ','.'),3),'_','.') AS Test2,
       REPLACE(PARSENAME(REPLACE(REPLACE(Column1,'.','_'), ' ','.'),2),'_','.') AS Test3,
       REPLACE(PARSENAME(REPLACE(REPLACE(Column1,'.','_'), ' ','.'),1),'_','.') AS Test4
FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

A good recent article by Jeff Moden

http://www.sqlservercentral.com/articles/Tally+Table/72993/

There are a bunch of other good ones, such as

http://www.sommarskog.se/arrays-in-sql.html

or

http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html

PluralSight Learning Library

 

[mossbs]

Brilliant Cheers Boris - did exactly what I needed.

Could you possibly break down how that works... just so I fully understand it...

 

[bborissov]

Check what PARSENAME() did in BOL

But as I said this will work if you have 4 values to split.
If you have more this will NOT work.

With several REPLACES() here I convert dot to underscore and back to dot, because PARSENAME() uses dot as separator.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Boris,

If we only have a limited number of parts, then we may apply XML splitting as shown in this thread

http://social.msdn.microsoft.com/Fo...l/thread/271aa938-2572-4698-94b6-77ad4c9bd998

But this may fail if there are some characters such as & in the string. In this case, I recommend the following blog post

http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html

PluralSight Learning Library