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.
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")>2In the sample above the text needs to be split into 4 rows as followsCollections>2Finance>2So 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 yourTableCROSS APPLY dbo.ParseValues(REPLACE(REPLACE(REPLACE(REPLACE(yourColumn,'Skill("',''),'")',''),' & ','.'),'>','.'),'.') ValsJimEveryday I learn something that somebody else already knew |
|
|
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 yourTableCROSS APPLY dbo.ParseValues(REPLACE(REPLACE(REPLACE(REPLACE(yourColumn,'Skill("',''),'")',''),' & ','.'),'>','.'),'.') ValsJimEveryday I learn something that somebody else already knew
ThanksHow 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 10:40:09
|
Try thisSELECT vals.*FROM yourTableCROSS APPLY dbo.ParseValues(REPLACE(REPLACE( REPLACE(yourColumn,'")','.') ,'Skill("',''),' & ','.'),'.') Vals JimEveryday I learn something that somebody else already knew |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-13 : 10:50:22
|
quote: Originally posted by jimf Try thisSELECT vals.*FROM yourTableCROSS APPLY dbo.ParseValues(REPLACE(REPLACE( REPLACE(yourColumn,'")','.') ,'Skill("',''),' & ','.'),'.') Vals JimEveryday I learn something that somebody else already knew
That is fantastic, Thanks |
|
|
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!JimEveryday I learn something that somebody else already knew |
|
|
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!JimEveryday I learn something that somebody else already knew
It certainly seemed to. |
|
|
|
|
|
|
|