Hi,

I'm trying to pull several different bits of text from the same string, but due to how the data is formatted, it's proving to be a headache.

The data is from a memo field that I (think) I've managed to get into a format I can manipulate using an SQL command.  I'm using CR8.5, by the way.

Now that I can call that data in a CR expression, I don't seem to be able to delineate what I need from that rest of what's in there.  Here's a sample of what that data looks like:

EnglishText1 | FrenchText1
EnglishText2 | FrenchText2
EnglishText3 | FrenchText3
...(etc)...

The goal is to get all the English text grouped together and displayed, then all the French text grouped together and displayed.  The difficulty comes from the fact that each English/French grouping seems to be separated by a carriage return and I have no idea how to try to search for this to separate a single grouping from the rest of the string.  If I can do that, I think I can manage to use Right() and Left() to pull the two pieces apart.

I've also considered trying to find the positions of all the "|" characters and just pull the text blocks from between consecutive pairings, then work each as they are pulled.  This would create a situation of having a single English text group at the beginning, several French/English groups through the middle, and one French group at the end.  The trick is that I don't know how many English/French pairings there will be from one data set to another, so knowing when to stop looping through it will be critical.

If anyone has any suggestions or recommendations on how best to approach this problem, I'd be grateful for the input.  I'm a CR novice by the way and have no access to CR's help files (which were never installed).  So I can see the syntax I need to used, just not the logic for how the functions work.

Thanks.

I think I understand the logic, but how do I "remove" a selection of text from my source data after I've concatenated it to the English and French strings that I'm building, so that I don't keep pulling the same text?

To bring it down to the most layman of terms, I think I can manage to copy data from my source to my new strings, but how do I cut it?

Also, I snooped around a bit more and found that chr(13) apparently can be used to designate a carriage return.  I tried this:

Stringvar array Pairings;
Pairings := Split({%Select_Root}, chr(13));
Pairings[1] & Pairings[2] & Pairings[3]

// %Select_Root is the SQL expression I'm using to grab the data from the memo field

It gave me partial results but it still didn't come out properly, aside from each pairing being jammed together without a space between them (I can fix that).  I haven't figured out what's wrong with it yet, but could this be viable for getting my English/French pairs together so I can just Right() and Left() them afterwards?

Thanks again.


Edited by Dreaming - 01 Feb 2010 at 1:34pm

The instr() function returns a number of where it finds the next pipe then you would do a string := right(number+1, string).  The plus one is to get past the pipe and you need to 'replace' your orginal string with the new 'shorter' string.  Again this needs to be in a while loop and it is best to have the len(string) > 0 as your condition.

This would probably save me some headaches since what I was playing with depended on locating carriage returns to delineate the sections I wanted to pull, but would probably break down at the last string since I'm not sure the memo even ends in a carriage return.  Some may and some may not.

This is the logic for the SQL expression, right?  Will those variables get passed back the CR form once it's finished running?

Figuring out the SQL stuff has been quite troublesome.  Several of the functions I'm using aren't even recognized by the SQL expression editor, yet still work, and the ones pulled right from the editor throw a syntax error.  Very confusing.  It took me two days to figure out the comment characters were /* */, instead of //.  Hadn't seen that before.

I'll give it a try with variables and see how it goes.  Fingers crossed.  Thanks very much for the tips.


Edited by Dreaming - 03 Feb 2010 at 7:13am

No, I would do this in Crystal formulas