SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Splitting data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/13/2012 :  08:00:26  Show Profile  Reply with Quote
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
2869 Posts

Posted - 11/13/2012 :  08:33:13  Show Profile  Reply with Quote
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 - 11/13/2012 :  10:18:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  10:40:09  Show Profile  Reply with Quote
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 - 11/13/2012 :  10:50:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  10:55:41  Show Profile  Reply with Quote
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 - 11/13/2012 :  11:03:52  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000