Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Splitting a string into fields Expand / Collapse
Author
Message
Posted Wednesday, March 06, 2013 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 03, 2013 11:50 AM
Points: 21, Visits: 70
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
Posted Wednesday, March 06, 2013 8:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:55 AM
Points: 532, Visits: 984
Take a look at this article by Jeff Moden.

http://www.sqlservercentral.com/articles/Tally+Table/72993/
Post #1427467
Posted Wednesday, March 06, 2013 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 03, 2013 11:50 AM
Points: 21, Visits: 70
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
Posted Wednesday, March 06, 2013 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,783, Visits: 12,893
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
Posted Wednesday, March 06, 2013 9:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:55 AM
Points: 532, Visits: 984
That Parsename always sneaks up on me..
Post #1427501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse