Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2007-11-15
Medium Priority
1,050 Views
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"

0
Question by:angeleam
• 2

LVL 101

Expert Comment

ID: 20294930
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

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 20294938
In checking the InStr function there may be an easier solution

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

mlmcc

0

LVL 28

Expert Comment

ID: 20296383
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

Author Closing Comment

ID: 31409480
Thank You.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…