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

Posted on 2009-02-12
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

    Sorry, I should say that these values are actually stored as a string, not array.
    LVL 4

    Expert Comment

    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 100

    Accepted Solution

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

    LVL 34

    Assisted Solution

    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

    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 34

    Expert Comment

    You're welcome.  Glad I could help.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    Hello, In my precious Article  ( saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now