Splitting a field with the delimiter "and"

[jbndylan]

I have a sql database and working in Crystal reports 10.

I am populating a form that will be sent to our parents.

I have a field that contains "wife's first name "and" husband's first name". ex. Kim and Robert

The report should look like:

Cell for Robert xxx-xxx-xxx
Cell for Kim xxx-xxx-xxx

Using
if {phone_full.phntype}= "cpd" then
"Cell for "+if instr({name_full.namefirst},'and',1)>0
then
mid({name_full.namefirst},(instr({name_full.namefirst},'and')+4))

I can get the first line regarding Robert.

I haven't been able to split the record to include only Kim's name

if {phone_full.phntype}= "cpm" then
"Cell for "+if instr({name_full.namefirst},'and',2)<0
then
mid({name_full.namefirst},(instr({name_full.namefirst},'and',1)+1))

This pulls Cell for "Kim and Robert"
Not sure if I'm using this correctly but I just want to pull out "Kim" from the {name_full.namefirst} field.

Can someone help?

Thank you so much!

Lisa H

[jggtz]

From Crystal Reports help

Split
Basic and Crystal syntax.

Overloads
Split (inputString)
Split (inputString, delimiter)
Split (inputString, delimiter, count)
Split (inputString, delimiter, count, compare)
Arguments
inputString is a String expression containing substrings and delimiters.
delimiter is an optional String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire inputString string is returned.
count is an optional number value of substrings to be returned. The value -1 indicates that all substrings are returned. If omitted, -1 is assumed.
compare is an optional Number indicating the kind of comparison to use when evaluating the delimiter string:
0 performs a comparison that is case-sensitive
1 performs a comparison that is case-insensitive
If omitted, a case-sensitive comparison is performed.

Note: Unlike in Visual Basic, in Crystal Reports if you omit an optional argument, you must omit all the following arguments. For example, if you do not specify a delimiter, you cannot specify count nor compare.

Returns
Array of String values.

Action
Split takes a String that contains a number of substrings, breaks it up into a specified number of substrings and returns an array containing the substrings.

Examples
The following examples are applicable to both Basic and Crystal syntax:

Split ("Chocolate Strawberry Pineapple")

Returns an array that contains 3 elements, "Chocolate", "Strawberry" and "Pineapple".

Split ("Chocolate//Strawberry//Pineapple", "//")

Returns an array that contains 3 elements, "Chocolate", "Strawberry" and "Pineapple".

Split ("Chocolate//Strawberry//Pineapple", "//", 2)

Returns an array that contains 2 elements, "Chocolate" and "Strawberry//Pineapple". The last element in the array is a concatenation of the 2nd substring and the remaining substring.

Split ("Chocolate and Strawberry and Pineapple", " And ", -1, 0)

Returns an array that contains 1 element, "Chocolate and Strawberry and Pineapple". The delimiter " And " cannot be matched.

Split ("Chocolate and Strawberry and Pineapple", " And ", -1, 1)

Returns an array that contains 3 elements, "Chocolate", "Strawberry" and "Pineapple". The delimiter " And " is matched with " and " regardless of the case.

Comments
This function is designed to work like the Visual Basic function of the same name.
If count, c, is less than the total number of substrings in inputString, then at most c substrings will be returned as elements in the resultant array. The last element in the array being a concatenation of the c-th substring and all the remaining substrings.