table design and data migration

Last Post 20 May 2007 08:00 AM by BiBert. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
12 May 2007 09:07 PM
Currently we have a design where we store customer notes in one record with a customer id.
Notes and customer info stored in one record.

Each customer may have one or more orders which stores in another table along with a customer id.

Here is the problem: everytime notes field filled up with comments we have to clean the notes field for the next note if customer calls.
We want to modify design and add separate table to store notes for each customer based on order.

Also, the problem here after the table design we need to migrate current notes data into the new table.

The record/s in a notes field may looks like this ( each note separated by semicolon ):

psolt12/4/01 01:46 PM: FORONA01KHG6 sometext; cdoonne12/8/03 01:37 AM: FORONA01KHG6sometext; iorowden4/22/2004 7:36:16 AM: sometext; uawson4/23/2004 12:31:56 AM: sometext

In this case it will be important to extract date from the notes.

or it can be like this :

;ruawson4/23/2004 12:31:56 AM: sometext


Is there a way to split data into several columns based on semicolon?

Thanks


BiBert
New Member
New Member

--
20 May 2007 08:00 AM
create a temporary table with a column for the data you want to migrate and all the destination columns you want
using the appropriate functions it is always possible to extract the different columns

here an example how you could get the dates out.
the other columns are much simpler
--start example
declare @a table (a varchar(222),b datetime)
insert @a(a) values ('iorowden4/22/2004 7:36:16 AM: sometext; uawson4/23/2004 12:31:56 AM: sometext')
insert @a(a) values (';ruawson4/23/2004 12:31:56 AM: sometext')
insert @a(a) values ('psolt12/4/01 01:46 PM: FORONA01KHG6 sometext; cdoonne12/8/03 01:37 AM: FORONA01KHG6sometext;')

update @a
set b = convert(datetime,
substring(a,
charindex('/',a) - 1 - case when isnumeric(substring(a,charindex('/',a) - 2,1)) = 1 then 1 else 0 end
, 1 + charindex(' ',a,charindex('/',a)) - charindex('/',a) + case when isnumeric(substring(a,charindex('/',a) - 2,1)) = 1 then 1 else 0 end
))

select *
from @a
--end example

is this what you are looking for?
succes
Bert
You are not authorized to post a reply.

Acceptable Use Policy