Converting memo field to a table

[pdatar]

Hi

In a CRM database on SQL 7 We have a call table with a memo field. This memo field is a field which gets appended as the call progresses thru Support. We would like to take this memo field and convert it into an events table where each entry is an separate event, a row in the events table

The memo field data is separated by ##### as a separator

Any ideas on how this can be done?

Thanks for reading

 

[mrdenny]

What data type is the field?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jbenson001]

And some sample data would be helpful.
Jim

 

[pdatar]

The data type is text 16
The eg below has 3 events all combined together. The no of events maybe different

Here is a sample (######is a separator)


###### gwinde: 29 Nov 2005 17:58:53 (+06:06)

rewrewrwer
fgsdfdsf

need to call the customer

###### gwinde: 30 Nov 2005 16:54:30 (+06:06)

customer wants to bild a vcxvxv(later with .Net). At the moment he usesxcvvxvv

The generated DLL should be animated cxvxvxcv.

He used our examples.
xcvxcvxcvcxv in Focus was terminated" (or similar) message.



=> we need a working example, which exactly uses the described scenario, so that he can continue his work with this example

###### edrewr: 30 Nov 2005 16:19:22 (+00:07)

sdfsdf


dsfsdf?



fsdfsdf


Thanks

 

[mrdenny]

This works assuming that all values of the text field are less than 8000 characters.

Put the value of the field into a varchar field. Then using the PATINDEX and SUBSTRING functions break the field appart. You can check BOL for info on these functions. Also search the forums for some examples.

If the field is greater than 8000 characters then you'll need to break the field apart into more than one variable (or loop through the text field, pulling out 8000 characters at a time into your variable. Some basic idea, just with some fancier loops in it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pdatar]

Thank you Denny!

We will give this a shot

 

[gmmastros]

You can tell if any of the lengths of the values exceed 8000 characters by running this...

Select Max(DataLength(<ColumnName>)) From <TableName>



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Joulius]

Look into TEXTPTR. I've done in the past something similar with what you are looking for and, if I will have the time the upcoming weekend, I will show you how.