Crystal Reports: How do I return the position in a deliminated string

Posted on 2009-02-12
Medium Priority
Last Modified: 2012-05-06
I'm trying to build some new reporting, and have hit a road block. Below is an example of the data I'm working with:

Field 1: TC5485,UR9283,TY_IN8097,PO9234

As you can see, each value is deliminated by a comma (,). I want to be able to extract all UR9283 values, and their associated vcode in field 2. I was thinking if I could find out what position in the array that the UR value was, I could use this to extract the position in the field two. However it can't be the position as in number or characters from the left as it will not always be OK, OK.

I have been able to extract the values from the first field by using:

IF InStr({Field 1},'UR') = 0 THEN {@Null} ELSE
MID({Field 1},(InStr({Field 1},'UR')),6)

This pulls out all the URxxxx strings, now I need to grab the associated value from Field 2.

Any advice? It can be in a completely seperate field to this value that I have extracted.
Question by:farmy

Author Comment

ID: 23629142
Sorry, I should say that these values are actually stored as a string, not array.

Expert Comment

ID: 23631783
Is the 'UR' string always found as the second field of the comma-delimited string?  If it is, then the following formula will work (using BASIC syntax):
dim x as string

x = mid ({@Field 2},(InStr({@Field 2},",")) + 1,2)

if trim(x) = "OK" then formula =  "OK" else formula = "Error"
LVL 101

Accepted Solution

mlmcc earned 1000 total points
ID: 23638710
Try it this way

Local stringvar array splitstr;
Local NumberVar i;

IF InStr({Field 1},'UR') = 0 THEN
    splitstr := Split(Field 1},',');
    for i := 1 to UBound(splitstr) do
         if left(splitstr[i],2) = 'UR' then

If the {@Null} doesn't work use ''

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 35

Assisted Solution

James0628 earned 1000 total points
ID: 23639063
Minor typo correction to mlmcc's formula:

    splitstr := Split(Field 1},',');

  should be

    splitstr := Split({Field 1},',');

 However, that formula doesn't actually work.  At least not under CR 10.  The last part just produces a True result (a boolean value).  Unless {@Null}  is a boolean, the formula won't even run.  If I remove the IF-ELSE and just leave the stuff after ELSE, I just get True as a result (whether UR is in the target string or not).

 This can be fixed by assigning the result of split({field2},',')[i] to a variable and outputting that variable after the for loop, but I have a couple of questions that may affect how, or if, this works.

 Do Field1 and Field2 always have the same number of elements?

 Do any of the elements ever contain a comma, or do they only appear as delimiters?

 Will there be any elements in Field1 that start with UR that you do _not_ want to include?

 Can you have more than one UR element in Field1?  If so, how do you want to handle that?  This could change things drastically.


Author Closing Comment

ID: 31546400
Thanks guys, I actually split my head open, and eventually came to the same result! Amazing what you can do when you try, I just thought there may have been something easier. Appreciate the help, both got me there.
LVL 35

Expert Comment

ID: 23669928
You're welcome.  Glad I could help.


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 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