Splitting a string into fields

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Splitting a string into fields

Splitting a string into fields

Rate Topic

Display Mode

Topic Options

Author

Message

Douglasjbell

Posted Wednesday, March 06, 2013 8:46 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20, Visits: 60

Hi All

I have a field called Defects which contain strings like the following. The string makeup is always the same.
Scrap : Part Assembly : Surface Defects : Scratch
Scrap : Part Assembly : Components : Wrong Components
Scrap : Part Assembly : Other : Change Over
Repair : Punching : Surface Damages : Crack

What I would like to do is create a view that splits the string by the : and have them displayed as individual fields.

I started of using CharIndex to find the : and then use left and the charindex to strip the first part out, but I felt doing it this way would involve a lot of LEFTs and RIGHTs to split up the whole string, is there an easier way to accomplish this?

Cheers

DJ

Post #1427463

Erin Ramsay

Posted Wednesday, March 06, 2013 8:50 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 421, Visits: 777

Take a look at this article by Jeff Moden.

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

Post #1427467

Douglasjbell

Posted Wednesday, March 06, 2013 9:05 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20, Visits: 60

Hi

Thanks for the reply, that whole post is a little above my head, but I will give it another read to see if I can make sense out of it.

Cheers

Dj

Post #1427475

ChrisM@Work

Posted Wednesday, March 06, 2013 9:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

If there really are always four elements, then you could use PARSENAME (look it up in BOL):

;WITH SampleData AS (
SELECT MyString = 'Scrap : Part Assembly : Surface Defects : Scratch' UNION ALL
SELECT 'Scrap : Part Assembly : Components : Wrong Components' UNION ALL
SELECT 'Scrap : Part Assembly : Other : Change Over' UNION ALL
SELECT 'Repair : Punching : Surface Damages : Crack')

SELECT 
	MyString, 
	Element1 = PARSENAME(REPLACE(MyString,' : ','.'),4), 
	Element2 = PARSENAME(REPLACE(MyString,' : ','.'),3), 
	Element3 = PARSENAME(REPLACE(MyString,' : ','.'),2), 
	Element4 = PARSENAME(REPLACE(MyString,' : ','.'),1) 
FROM SampleData

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1427481

Erin Ramsay

Posted Wednesday, March 06, 2013 9:37 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 421, Visits: 777

That Parsename always sneaks up on me..

Post #1427501

« Prev Topic | Next Topic »

Permissions