Search code examples
sqlarrayssyntaxsplitcrystal-reports

SPLIT Error From Null Data

I am having issues splitting data up from one of our character user fields in our database. An example of the data we are entering in the field is: 

abc-123-456; abc-789; 1234567; abcdefghi

I am wanting to split the data every time there is a ; I created 4 formulas and placed them in the report header:

Split({STANDARD.CUSER6}, ";") [1]
Split({STANDARD.CUSER6}, ";") [2]
Split({STANDARD.CUSER6}, ";") [3]
Split({STANDARD.CUSER6}, ";") [4]

I have also tried a formula like:

if isnull({STANDARD.CUSER6}) then ""
else
split({STANDARD.CUSER6},";") [1]

This works great when there are 4 "groups" of data in the character user field, but anytime there are none or less than 4 "groups" of data I receive

A subscript must be between 1 and the size of the array.

New to report writing, so I'm not sure if I should be using a split formula or something else.

All feedback is appreciated!

Solution

  • You can use the UBound-function to get the highest array index:

    The formulas should look like this:

    Group 1

    If UBound(Split({STANDARD.CUSER6}, ";")) > 0 Then
    Split({STANDARD.CUSER6}, ";")[1]
Else
    ""

    Group 2

    If UBound(Split({STANDARD.CUSER6}, ";")) > 1 Then
    Split({STANDARD.CUSER6}, ";")[2]
Else
    ""

    Group 3

    If UBound(Split({STANDARD.CUSER6}, ";")) > 2 Then
    Split({STANDARD.CUSER6}, ";")[3]
Else
    ""

    Group 4

    If UBound(Split({STANDARD.CUSER6}, ";")) > 3 Then
    Split({STANDARD.CUSER6}, ";")[4]
Else
    ""

    Note: (Array indexes start at 1 instead of 0 in Crystal Reports.)