Help with stripping characters out of a field

[dchard]

In Crystal 9 I have a formula (below) that searches the SO_Detail.PRTNUM_28 field and if it finds /SP or SP/ strips out those three characters. I need to expand the formula so that if it finds just SP it strips that out as well as the /SP and SP/. The formula also needs to not look at the first four characters of the SO_Detail.PRTNUM_28 field as I have some data that starts with SPA and BSP that I do not want stripped..

Thanks

Deb

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}

 

[bdreed35]

try this:

replace(replace(replace(mid({SO_Detail.PRTNUM_28},3),"/SP",""),"SP/",""),"SP","")

~Brian

 

[lbass]

I think you need to supply sample data that shows the variations in the field.

-LB

 

[dchard]

The SO_Detail.PRTNUM_28 contains data that looks like this
SPA9000 (i would not want to strip this at all)
BSP123 (i would not want to strip this at all)
OMH6700/SP01 (this should be stripped to OMH6700)
OMH6701SP/01 (this should be stripped to OMH6701)
OMH6700SP01 (this should be stripped to OMH6700)
OMH6700 (this would be evaluated but not stripped)

 

[lbass]

I think bdreed's solution should work fine.

-LB

 

[dchard]

Well the bdreed solution does seem to get rid of the SP but it is also stripping off the first two characters of the field. I don't want those stripped off, I just don't want Crystal to look at them when considering the SP replace

Thanks

Deb

 

[Turkbear]

Hi,
Try a 2 stage approach:

StringVar CandidateStr = mid(SO_Detail.PRTNUM_28 ,3);
replace(replace(replace(CandidateStr,"/SP",""),"SP/",""),"SP","")

Note: Check on my Parens, I may have mis-counted, but you should get the idea.

 

[lbass]

bdreed's solution shouldn't strip the initial "SP"--are you sure you copied his formula correctly? It uses the mid function to skip the first three values.

-LB

 

[dchard]

Yes, it strips off the first three characters of the field SO.DETAIL_Partnum, and I copied it and pasted it into Crystal. The original post contains my original formula which does work. I incorrectly stated that I was trying to remove three characters from the field. Actually, I am trying to remove the SP/, the /SP and SP along with all other characters to the left. The problem that I am having is that some of my data contains SP somewhere within the first three positions of the field and I want to not look at those first three positions, not strip them off.

Deb

 

[lbass]

Yes, forgot to add on the first values, plus I think you mean you want everything stripped from the right if one of the SP elements is found. Try the following:

left({SO_Detail.PRTNUM_28},3)+
(
if instr(mid({SO_Detail.PRTNUM_28},4),"/SP") > 0 then
mid({SO_Detail.PRTNUM_28},4,instr(mid({SO_Detail.PRTNUM_28},4),"/SP")-1) else
if instr(mid({SO_Detail.PRTNUM_28},4),"SP/") > 0 then
mid({SO_Detail.PRTNUM_28},4,instr(mid({SO_Detail.PRTNUM_28},4),"SP/")-1) else
if instr(mid({SO_Detail.PRTNUM_28},4),"SP") > 0 then
mid({SO_Detail.PRTNUM_28},4,instr(mid({SO_Detail.PRTNUM_28},4),"SP")-1)
)

-LB