Break up a Memo Field

[Dan Blank]

I have a client that has 6 memo fields in one table. They have come up against the 2 gig limit, so they can't add any more records. Each memo field has less than 1,000 characters in the memo fields.

I want to bread each memo field up into 4 different character fields of 255 characters.

Anyone have a suggestions on how to best accomplish this?

Thanks,

Dan

[Tom Cone Jr]

RE: Break up a Memo Field

Not sure that changing the table structure will save space. Each character field you define will be filled with blank spaces even if the text you place there does not run the full width of the field.

Having said that, I think the answer to your question lies reading the memo field text into a character type variable. This will give you one long string of characters, punctuation marks, and linefeed carriage return pairs. You then would have to parse the resulting string to assign portions of it to different table fields.

-- tom

[Dan Blank]

RE: Break up a Memo Field

Hi Tom,

Thanks for responding! I can get the memo field into a character varialble. It is the parsing that I don't know how to do. Never 'parsed' before.

Any suggestions?

Thanks,

Dan

[Tom Cone Jr]

RE: Break up a Memo Field

Others will probably have better suggestions.

But a brute force approach would determine the length of the text in the variable, and then divide it by 4. Then you could use the substr() function to carve blocks of characters out of the single long string. You could then examine the final character in each block. If it's not a space or a crlf pair it's probably in the middle of a word, So you'd have to borrow characters from the next block.

An alternative would be to step through the string from left to right counting chars as you go. As each char is retrieved you examine it to see if its an alphanumeric, a space, or a cr or a lf. As each is processed you tack it onto a separate variable you build one char at a time. Once you get near the field width you're shooting for you keep counting and processing each char until you hit a space or crlf pair. The new variable you're building is saved to disk and emptied. You resume processing characters from the last position, continuing until all are processed.

-- tom

[csda1]

RE: Break up a Memo Field

Dan

The easiest way to approach this is to break up the memo field's into separate tables (each having a 2 Gig limit - it might be more under NTFS file system of XP depending upon where the limitation is). Create 6 tables having a 1:1 link field and a memo field.

Then copy each memo field into each of the tables to hold it.

As background, all memo fields in a table are stored in the same memo file. If you don't compact the memo field table it will grow every single time you save a memo field by about 512 bytes. To compact it, you basically need to copy the table records to a new table, and then move the copy back to the original location.

Regards,

Ira

[Tom Cone Jr]

RE: Break up a Memo Field

Ira, thanks. You've helped me see the forest, after I became engrossed in counting leaves on one of the trees! -- tom

[Dan Blank]

RE: Break up a Memo Field

Hi Ira, and Tom,

Ira, thanks for the suggestion I will probably go this route as it is the easier way to go. I just hope this will preform well over a network. I really hate using memo fields over a network. We shall see!

Tom, Your idea will probably be my second choice if the memo fields cause to many problems over the network.

Thanks for your help!

Dan

[Peter.Greulich]

RE: Break up a Memo Field

Dan,

Not sure what your concern is with "using memo fields over a network". I have lots of tables w. memos and I use them over the network everyday. But I do try to follow Peter Wayne's advice - a.k.a. "Memo fields that work".

Peter