How to Split a Field's Values Where a Character Exists in Crystal Reports

Written by barry kaye
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Split a Field's Values Where a Character Exists in Crystal Reports
Splitting field data at a specified character in Crystal Reports. (Medioimages/Photodisc/Valueline/Getty Images)

Splitting the value of an alphanumeric field at a specific character in Crystal Reports is fairly straightforward. The premise is to split the field into two parts at the numeric position of the desired character. We can capture each part's value in a separate formula using the built-in string functions. The first part will capture the fields first position to the split position. The second part will capture

from the split position +1 to the last position.

Skill level:
Moderately Easy

Other People Are Reading

Things you need

  • Crystal Reports

Show MoreHide

Instructions

  1. 1

    Open a new formula and define string variables. Note: // define comments

    stringVar s := 'abcdefghijklmnopqrstuvwxyz'; // s = constant or database field value

    stringVar c := 'm'; // c = any character in s to define the split position

    numberVar L := length(s); // L = length of s (aka: last position)

    numberVar I := instr(s, c); // I = numeric field position of c (ex: m=13)

  2. 2

    Following the variables of Step 1, define the first split part using the variables as arguments in the built-in String Functions.

    // mid takes string (s) from the numeric starting position (1) to ending position (I).

    mid(s,1,i)

  3. 3

    Following the variables of Step 1, define the second split part using the variables as arguments in the built-in String Functions.

    // mid takes string (s) from the numeric starting position (i+1) to ending position (L).

    mid(s,i+1,L)

  4. 4

    Decide how to use your defined split parts.

    1) Displayed as a carriage returned result at the split position

       // CHR(13) is the ASCII for a Carriage Return
    
    
    
          mid(s,1,I) + chr(13) + mid(s,i+1,L)
    

    2) Displayed as two separate fields.

    - Name formula to Part 1 and name a copy as Part 2
    
    - Keep the variable declarations in each formula
    
    - Keep mid(s,1,i) for Part 1
    
    - Keep mid(s,i+1,L) for Part 2
    
    - Place Part 1 and Part 2 on report independently
    

Tips and warnings

  • If variables were defined as shared or global, they could be reused without redeclaring in Part 2

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.