Splitting a string into fields

• Douglasjbell

  SSC Veteran

  Points: 289

  More actions

  March 6, 2013 at 8:46 am

  #269290

  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

• Erin Ramsay

  SSCrazy Eights

  Points: 9728

  More actions

  March 6, 2013 at 8:50 am

  #1594018

  Take a look at this article by Jeff Moden.

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

• Douglasjbell

  SSC Veteran

  Points: 289

  More actions

  March 6, 2013 at 9:05 am

  #1594025

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 6, 2013 at 9:10 am

  #1594029

  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

• Erin Ramsay

  SSCrazy Eights

  Points: 9728

  More actions

  March 6, 2013 at 9:37 am

  #1594049

  That Parsename always sneaks up on me..