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

Posted on 2007-11-15
Last Modified: 2013-12-19
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"

Question by:angeleam
  • 2
LVL 100

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');

LVL 100

Accepted Solution

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

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


LVL 28

Expert Comment

by:Naveen Kumar
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


Author Closing Comment

ID: 31409480
Thank You.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL 6 58
Oracle Public Synonyms and Privileges 2 65
Oracle Listener Not Starting 11 44
Page n of M in Crystal reports not calculating correctly 4 16
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question