Business Objects, Crystal Reports, XI, Extracting Data from a memo field.

How do I extract text from a memo field in crystal. For example:

Subject: (Order# 9987) This is an order. (Order# 9976) This is the second order.

I want to only display the text after the last order number. For this example I would only want to explay "This is the second order"

Subject: (Order# 9987) This is an order. (Order# 9976) This is the second order.  (Order# 9982) This is the third order.

For this example I would only want to print "This is the third order"



angeleamAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
In checking the InStr function there may be an easier solution

NumberVar ThisLoc;
ThisLoc := InStrRev({YourMemoField},"This");
Mid({YourMemoField},ThisLoc)

mlmcc

0
 
mlmccCommented:
What is in the txt field?

One way you can do this is with the split function

StringVar Array Orders[];
NumberVar  NumOrders;
NumberVar ThisLoc;

Orders := Split({YourMemoField},'(Order#');
NumOrders := Ubound(Orders);
ThisLoc := Instr(Orders[NumOrders],'This');
Mid(Orders[NumOrders],ThisLoc);

mlmcc
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
In oracle, you can do it like this :

select substr(clob_field,instr(clob_field,') ',-1)+2)
from your_table;

also to test it, you can use the below :

SELECT SUBSTR('(Order# 9987) This is an order. (Order# 9976) This is the second order.  (Order# 9982) This is the third order. ',
              INSTR('(Order# 9987) This is an order. (Order# 9976) This is the second order.  (Order# 9982) This is the third order. ',
                  ') ',-1)+2
             )
FROM dual

Thanks
0
 
angeleamAuthor Commented:
Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.