Parsing Data from string

[dcjames]

I have a field that contains the following information:

CN=FirstName LastName,OU=Container,DC=Container,DC=Container

What I'm needing to do is to parse out just the FirstName and LastName values.

Any help is greatly appreciated.

 

[FireGeek21]

If it is separated by a comma then use:

Left({field}, InStr({field}, ",")-1)

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[dcjames]

This looks great...what can I do in the case that there is no data in the field? I'm getting an error when running the report and it appears that's due to some fields missing data

 

[FireGeek21]

Make it an IF statement:

IF {field} = ""
THEN ""
ELSE Left({field}, InStr({field}, ",")-1)

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[dcjames]

FireGeek21, thanks for the follow up. The If statement appears to work fine but when I go to run the report I get

"String Length is less than 0 or not an integer"

I thought by accounting for the null values this problem would of been resolved...not sure where to go with it now

 

[FireGeek21]

Switch up the IF statement:

IF InStr({field}, ",") <= 0
THEN ""
ELSE Left({field}, InStr({field}, ",")-1)

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[dcjames]

FireGeek - This looks great! One last thing ( sorry )...I noticed that the CN= before the first and last name is still appearing...I was hoping to parse out just the full name ( CN=FirstName LastName,OU=Container,DC=Container,DC=Container )

 

[FireGeek21]

Ah, I thought that was a field name of sorts.

IF InStr({field}, ",") <= 0
THEN ""
ELSE Right(Left({field}, InStr({field}, ",")-1), Len(Left({field}, InStr({field}, ",")-1))-3)

-or-

IF InStr({field}, ",") <= 0
THEN ""
ELSE Mid(Left({field}, InStr({field}, ",")-1), 4, Len(Len(Left({field}, InStr({field}, ",")-1))-3)


I hope I have enough () -

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[AndyMc]

You can try (if you don't put the 3rd argument in the Mid function, it goes the end of the field starting from the 2nd argument):

IF InStr({field}, ",") <= 0
THEN ""
ELSE mid(Left({field}, InStr({field}, ",")-1),4)

 

[FireGeek21]

AndyMc - That works too. Thanks.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[dcjames]

Thank you all for the responses! You made this a lot easier on me