help trimming a string field 4

[dchard]

I need help writing the formula for the following scenario.

I have a text data field that contains a part number. Some of the part numbers end in /SPxx and some end in SP/xx where xx is a random number. I only want the data prior to the /SP or SP/
LDC3916
LDC3916/SP01
LDC9615SP/02
LDC2926339/SP03
LDC2926339SP/00

In the samples above, I want to strip off everything so the data would like
LDC3916
LDC3916
LDC9615
LDC2926339
LDC2926339

-deb

 

[rosemaryl]

Dear Deb,

There is probably a more elegant way to do this, but as a quick solution this should work for you. Simply remove the variable declaration line and replace all occurances of "test" with your field the data is stored in.

stringvar test := '12456789/SP';
if InStr (test,'/SP' ) > 0
    then
    Mid(test,1,InStr (test,'/SP' )-1)
    else
    if InStr (test,'SP/' ) > 0
    then
    Mid(test,1,InStr (test,'SP/' )-1)
else
test

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[bdreed35]

I think creating a formula with this in it will work for you based upon what you have stated:

split(replace({table.field},"SP","/"),"//")[1];

~Brian

 

[rosemaryl]

Dear Brian,

Nice!* As I said, I was sure there was a more elegant solution.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[dchard]

Thanks to both of you for your reply, I ended up using RosemaryL's solution, with a minor modification,
if InStr ({SO_Detail.PRTNUM_28},'/SP' ) > 0
then
Mid({SO_Detail.PRTNUM_28},1,InStr ({SO_Detail.PRTNUM_28},'/SP' )-1)
else
if InStr ({SO_Detail.PRTNUM_28},'SP/' ) > 0
then
Mid({SO_Detail.PRTNUM_28},1,InStr ({SO_Detail.PRTNUM_28},'SP/' )-1)
else
{SO_Detail.PRTNUM_28}

This worked for me.

deb

 

[rosemaryl]

Dear deb,

Glad to have helped, however just to note, Brian's solution is more elegant. In my testing it worked flawlessly. So, I was wondering why you went with the less elegant one?

Was it just easier for you to understand?

If that was the case, a little explanation may help and then you can use that knowledge in the future.

The replace function he used takes a string, the value to be replaced, and the replacement value and then returns the string replaced.

So in his example looking at just the replace part:

Any string with SP in it gets replaced with a /.

So regardless of whether it is sp/ or /sp, the string is retunned as '123456//' to the formula.

He then uses the split function to split that returned string at the '//' so that you end up with 123456 and // in a two string array and then uses the [1] to tell the split function to return the first value in the array which is your string!

split(replace({table.field},"SP","/"),"//")[1];

I hope that helps to explain his formula, it really was a very nice solution.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Nuffsaid]

Rosemary,

* for taking the time to explain.

Nuffsaid

 

[kchaudhry]

Rosemaryl,

Excellent explanation. One star from me too.




Kchaudhry

 

[dchard]

Rosemaryl,

Thanks for the explanation, no I did not understand what exactly it was doing, and when I pasted inthe formula I got some kind of syntax error that I did not feel like figuring out. So, even though the second solution was more elegant, yours was the quickest to implement. I will give the other method a try when I have a bit more time, and thank you for the explanation, it will helped a lot.

deb

 

[bdreed35]

This is not intended to be sarcastic, but the functions I used are explained in the help files that come with Crystal Reports. I think that it is often forgotten about, and overlooked as a resource.

~Brian