Data Migration

• sstecher

  Ten Centuries

  Points: 1057

  More actions

  December 14, 2004 at 10:49 am

  #87341

  Gurus,

  Quick Question.  I'm migrating data from one database to another.  Everything in my script works great except for one part (the contact_name field).  In the old database names were stored as

  Jones, Larry/Dee  or Smith, Gene/Wilma

  In the new db schema I'm wanting to seperate the contact names into a first_name and last_name field.  I do know in doing this I'll probably loose one of the first names, but who cares.

  Is there a way I can strip everything off before the "comma" and use this as my last name.  Then strip everything between the "comma" and the "/" as the first name?

  Hopefully this is clear as to what I'm wanting to do.

  Thanks

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  December 14, 2004 at 12:03 pm

  #533826

  How about this?

  --create a table with one name column and insert data

  create table oldname

  (name varchar (20))

  insert into oldname values ('Jones,Larry/Dee')

  insert into oldname values ('Smith,Gene/Wilma') 

  select * from oldname

  name                

  --------------------

  Jones,Larry/Dee

  Smith,Gene/Wilma

  (2 row(s) affected)

  --create table with separate name columns and insert data

  create table newname

  (lastname varchar(10),

   firstname varchar (10))

  insert into newname

  select substring(name,1,charindex(',',name)-1),

         substring(name,charindex(',',name)+1,charindex('/',name)- charindex(',',name)-1)

  from oldname 

  select * from newname

  lastname   firstname 

  ---------- ----------

  Jones      Larry

  Smith      Gene

  (2 row(s) affected)

  Greg

   

  Greg