Split Data into two fields

• Keith Edwards

  Default port

  Points: 1446

  More actions

  March 1, 2007 at 9:08 am

  #175492

  I am trying to split a field named Quad from a table called QuadTest into two fields. I believe I can get most of the data to look like the sample below. The actual table has around 50,000 records. Ideally I want to have the data in two fields called Quad, Quad2. I feel like I am pretty close, but I would appreciate some help getting over the hump. I have tried a couple functions, but they didn't seem like they could pull data from my table Quad. Thanks

   

  Noblesville,Fishers

  Omega,Riverwood

  Jasper,Alfordsville

  BeechGrove,Greenwood

  IndianapolisEast,Fishers

  Riverwood,Omega

  CorydonWest,CorydonEast

  Henderson,Harrison

  Etna,Troy

  Perry,SaltCreek

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 1, 2007 at 9:34 am

  #692656

  here's an example: you need to decide what to do with columns that have multiple or no commas, but here you go:

  create table QUADTEST(namefield varchar(60) )

  insert into QUADTEST(namefield) VALUES('Noblesville,Fishers')

  insert into QUADTEST(namefield) VALUES('Omega,Riverwood')

  insert into QUADTEST(namefield) VALUES('Jasper,Alfordsville')

  insert into QUADTEST(namefield) VALUES('BeechGrove,Greenwood')

  insert into QUADTEST(namefield) VALUES('IndianapolisEast,Fishers')

  insert into QUADTEST(namefield) VALUES('Riverwood,Omega')

  insert into QUADTEST(namefield) VALUES('CorydonWest,CorydonEast')

  insert into QUADTEST(namefield) VALUES('Henderson,Harrison')

  insert into QUADTEST(namefield) VALUES('Etna,Troy')

  insert into QUADTEST(namefield) VALUES('Perry,SaltCreek')

  insert into QUADTEST(namefield) VALUES('NOCOMMAS')

  select left(namefield,CHARINDEX(',', namefield) -1)  AS field1,substring(namefield,CHARINDEX(',', namefield)+1,60)  as field2

  from QUADTEST

  WHERe CHARINDEX(',', namefield) > 0

  results;

  field1	field2
  Noblesville	Fishers
  Omega	Riverwood
  Jasper	Alfordsville
  BeechGrove	Greenwood
  IndianapolisEast	Fishers
  Riverwood	Omega
  CorydonWest	CorydonEast
  Henderson	Harrison
  Etna	Troy
  Perry	SaltCreek

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Keith Edwards

  Default port

  Points: 1446

  More actions

  March 1, 2007 at 10:28 am

  #692672

  Looks good. Thanks for your help.

   

  Keith

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  March 1, 2007 at 12:27 pm

  #692696

  For these migration type of needs it usually works good to have a multiple level attack.

  Create a tmp table with an identifying column (or two) the sourcefield Quad and the two targetFields quad1 & quad2.

  Create the first most likely query (above)

  Run the first query. Identify all those that failed for this rule and create additional queries.

  Repeat...

  Reintegrate temporary table with original source table.

  btw: sometimes this still leaves a person with a load of garbage the business doesn't have an answer for. Last gasp you take a non-answer as all 'garbage' entries get inserted in field Quad1 or 2.