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
 Splitting data

Author  Topic 

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-13 : 08:00:26
Looking for a bit of help if possible.

I have data in one cell of a table that I need to split into seperate columns.

Skill("Collections")>2 & Skill("Finance")>2

In the sample above the text needs to be split into 4 rows as follows

Collections
>2
Finance
>2


So anything in bold should be discarded.

There could be 2 rows, 4 rows, 6 rows etc depending on what text needs to be split, but will always be in multiples of two.

Does anyone have any thoughts re how I can achieve this. I am very much an SQL novice.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 08:33:13
This will work for the sample provided. dbo.parseValues is a function thats splits delimited strings. You can search this site to find it.

SELECT vals.*
FROM yourTable
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE(REPLACE(REPLACE(yourColumn,'Skill("',''),'")',''),' & ','.'),'>','.'),'.') Vals

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-13 : 10:18:59
quote:
Originally posted by jimf

This will work for the sample provided. dbo.parseValues is a function thats splits delimited strings. You can search this site to find it.

SELECT vals.*
FROM yourTable
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE(REPLACE(REPLACE(yourColumn,'Skill("',''),'")',''),' & ','.'),'>','.'),'.') Vals

Jim

Everyday I learn something that somebody else already knew



Thanks

How do I change it so it keeps >2 as currently it just shows 2?

I tried to work it our but getting confused with the syntax.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 10:40:09
Try this

SELECT vals.*
FROM yourTable
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE( REPLACE(yourColumn,'")','.') ,'Skill("',''),' & ','.'),'.') Vals



Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-13 : 10:50:22
quote:
Originally posted by jimf

Try this

SELECT vals.*
FROM yourTable
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE( REPLACE(yourColumn,'")','.') ,'Skill("',''),' & ','.'),'.') Vals



Jim

Everyday I learn something that somebody else already knew



That is fantastic, Thanks
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 10:55:41
You're welcome. I hope it works on your entire dataset!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-13 : 11:03:52
quote:
Originally posted by jimf

You're welcome. I hope it works on your entire dataset!

Jim

Everyday I learn something that somebody else already knew



It certainly seemed to.

Go to Top of Page
   

- Advertisement -