I'm trying to figure out how to pull information from a memo field into a VarChar field, but have it populate the different columns around the carriage returns that are in the memo field.

Inside the memo field it looks like this:

4/29/2007 4:36:02 PM, by Default User
$300.00 - Quarterly, totaling $1,200.00 from 1/1/2007 to 12/31/2007

I wrote a CAST SQL that pulls it out to a VARCHAR field but it didn't give me the results that I'm looking for.

Thoughts and suggestions are appreciated.

JansB


Can you let us have some more details please. What result do you want, and what was your SQL?

Roy Lambert [Team Elevate]

Sure. I am wanting to break down the memo field into different columns based on the carriage return as need be.

Initially I'm creating the SQL to add in columns as I needed in order to hold the information that the Update SQL is pulling.

Alter table Pledges
 Add TestA VarChar(100)

Update Pledges
Set TestA = Cast (NOTES as VarChar(100));

This is just pulling all of the memo information out of the Memo field into a VarChar column. I'll add in more columns into the SQL as needed because the information in the Memo field can span many years of different pledge records. But what I'm trying to figure out is if it is possible to break the Memo field around the carriage return into the different columns to give me good detailed information to work around.

If i understand this correctly then there is nothing automatic to break
memo at CRs - you need to use POS and SUBSTRING functions to extract the
values which gets complex if there are many of them.

Can you do this in Delphi instead as it would be easier i think ?

Raul


On 5/7/2012 9:51 AM, JansB wrote:
> Sure. I am wanting to break down the memo field into different columns based on the carriage return as need be.
>
> Initially I'm creating the SQL to add in columns as I needed in order to hold the information that the Update SQL is pulling.
>
> Alter table Pledges
>    Add TestA VarChar(100)
>
> Update Pledges
> Set TestA = Cast (NOTES as VarChar(100));
>
> This is just pulling all of the memo information out of the Memo field into a VarChar column. I'll add in more columns into the SQL as needed because the information in the Memo field can span many years of different pledge records. But what I'm trying to figure out is if it is possible to break the Memo field around the carriage return into the different columns to give me good detailed information to work around.
>

Raul wrote:

If i understand this correctly then there is nothing automatic to break
memo at CRs - you need to use POS and SUBSTRING functions to extract the
values which gets complex if there are many of them.

Can you do this in Delphi instead as it would be easier i think ?

Raul

I don't have any training with programming languages, I work solely with SQL queries in the databases that my company handles. I'm sure that you are correct in that Delphi or one of the other langauges would make quick work of situations like this. I just like digging in with information like this and seeing if I can get it to work before moving up the chain in the company to get another department to handle the work. Thank you very much for the attention and suggestions.

JansB


I think I understand what you're asking, but don't be surprise if I'm way off.

My preferred route would, for the later versions of DBISAM, be to use a user defined function (check out customising the engine in the pdf manual). That way you could have something like

Update Pledges Set TestA = ExtractLine(NOTES,3)

ExtractLine would essentially be a Delphi function working with stringlists.

You should be able to achieve a similar result using SUBSTRING and POSITION providing you know how many lines there are and don't mind typing some horrible SQL eg to do the same as above (untested)


Update Pledges Set TestA =

SUBSTRING(SUBSTRING(NOTES FROM POSITION(#13,NOTES)+2 FOR 9999999) FROM 1 FOR POSITION(#13, SUBSTRING(NOTES FROM POSITION(#13,NOTES)+2 FOR 9999999))

and it get worse if there are more lines


Roy Lambert [Team Elevate]

ps I'm pretty sure the example is wrong

Roy Lambert wrote:

JansB


I think I understand what you're asking, but don't be surprise if I'm way off.

My preferred route would, for the later versions of DBISAM, be to use a user defined function (check out customising the engine in the pdf manual). That way you could have something like

Update Pledges Set TestA = ExtractLine(NOTES,3)

ExtractLine would essentially be a Delphi function working with stringlists.

You should be able to achieve a similar result using SUBSTRING and POSITION providing you know how many lines there are and don't mind typing some horrible SQL eg to do the same as above (untested)


Update Pledges Set TestA =

SUBSTRING(SUBSTRING(NOTES FROM POSITION(#13,NOTES)+2 FOR 9999999) FROM 1 FOR POSITION(#13, SUBSTRING(NOTES FROM POSITION(#13,NOTES)+2 FOR 9999999))

and it get worse if there are more lines


Roy Lambert [Team Elevate]

ps I'm pretty sure the example is wrong

This gives me something to work with that I didn't consider. I'll try working through this as best I can before starting to work up the chain. If I can write a couple yards of horrible code and get it to do what I need it do, then I can start going back through it to see where I can tweak and clean it up. Thank you.