Split Data into two fields

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

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 · Answer 2

Keith Edwards

Default port

Points: 1446

March 1, 2007 at 10:28 am

#692672

Looks good. Thanks for your help.

Keith

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 3

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.