| Author |
 Topic  |
|
|
sprotson
Yak Posting Veteran
70 Posts |
 Posted - 11/13/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 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 |
 |
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 11/13/2012 : 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. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 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 |
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 11/13/2012 : 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 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 10:55:41
|
You're welcome. I hope it works on your entire dataset!
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 11/13/2012 : 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.
|
|
|
| |
Topic |
|
Splitting data
