Parse Two Delimited Table Columns Into Multiple Records

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  October 22, 2014 at 1:16 pm

  #311961

  I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

  CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '2', 'M231_2)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '3', 'M231_1)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '4', 'M231_2)

  The results I am looking to achieve are:

  PLanIDGroupIDSubGroupIDAddOnCodeExternalCode

  101000005LAA1M231_1

  101000005OCA2M231_2

  101000005UCA3M231_3

  101000005XCA4M231_4

  201000005LAA1M231_1

  201000005OCA2M231_2

  201000005UCA3M231_3

  201000005XCA4M231_4

  Is there an SQL statement that can be used to accomplish this?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  October 23, 2014 at 10:26 am

  #1754417

  You should read this article, http://www.sqlservercentral.com/articles/72993/, by Jeff Moden. As far as I'm concerned that's got the best method for splitting strings.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question