substring formula

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: lphenderson — 8 Apr 2010 at 7:36am

I have a description field that begins with any number of numeric characters. I want to strip the numbers off and just leave the alpha part of the field. I was trying a substr formula:

substr('{OBVARC_txt.DESCRIPTION}',instr('{OBVARC_txt.DESCRIPTION}',' ',-1)+1) but Crystal XI is not recognizing it. The values in the field can have any number of numeric characters preceding the alpha portion. Is this clear enough that someone can help?

Posted By: kevlray — 8 Apr 2010 at 8:09am

There is no substring in Crystal, use the mid function.

Posted By: lphenderson — 8 Apr 2010 at 8:19am

Don't you have to use exact positions in the string with the Mid function?

Mid (, )

Posted By: lphenderson — 8 Apr 2010 at 8:42am

Can you use a nested ifthenelse to 'test' each position for an alpha character? When the result is finally 'true' how would you define 'give me the rest of the field'?

Posted By: kevlray — 8 Apr 2010 at 9:38am

mid(string, start, len), but from looking at the formula presented, it looks like a left(string, len), might work. Not sure what a -1 in the instr function will do. The documentation only mentions using a 0 or a 1.

Posted By: DBlank — 8 Apr 2010 at 9:58am

Not my strength here but

try a while do ....

mid({OBVARC_txt.DESCRIPTION},

(Local StringVar inString := {OBVARC_txt.DESCRIPTION};

Local NumberVar strLen := Length (inString);

Local NumberVar result := -1;

Local NumberVar i := 1;

While i <= strLen And result = -1 Do

(

Local StringVar c := inString ;

   If NOT NumericText (c) Then

      result := i;

   i := i + 1;

);

result)
)

Edited by DBlank - 08 Apr 2010 at 10:00am

Posted By: lphenderson — 9 Apr 2010 at 1:01am

It's a great formula, just doesn't seem to work to change the field. When I use left or right it does limit the field to only 1 character. The mid lets it all go through. I really appreciate you all taking the time to help. Thanks.

Posted By: DBlank — 9 Apr 2010 at 4:31am

Sorry, i tconverted part of it to using italics instead of displaying it, i changed i to a to get it to display correctly..

mid({OBVARC_txt.DESCRIPTION},

(Local StringVar inString := {OBVARC_txt.DESCRIPTION};

Local NumberVar strLen := Length (inString);
Local NumberVar result := -1;
Local NumberVar a := 1;
While a <= strLen And result = -1 Do
(
   Local StringVar c := inString [a];
   If NOT NumericText (c) Then
      result := a;
   a := a + 1;
);
result)
)

Edited by DBlank - 09 Apr 2010 at 4:34am

Posted By: lphenderson — 9 Apr 2010 at 4:41am

This is very exciting because it does strip the numerics off. I am left with a leading space. Can you tell me where to add the statement for left trim to what is left?

Thanks!

Posted By: lphenderson — 9 Apr 2010 at 4:53am

One little 'hitch' I just found. for rows where there are groups of numerics separated by a space, this just strips off the beginning numbers but leaves the subsequent groups of them. Suggestions?

Posted By: DBlank — 9 Apr 2010 at 5:02am

So you want to replace any numeric or blank field field with a ""?

e.g. ("123abc 9 xyz" becomes "abcxyz")

Posted By: lphenderson — 9 Apr 2010 at 5:13am

Not quite. I surely hope this is not impossible with Crystal. My data looks like the following examples:

1 Methadone 2mg

180 253748 SENNA PLUS TAB

12 Heparin Solution 500ml

The current formula seems to work fine for the rows with only one 'set' of numbers in front. The second example row only strips the 180 but leaves the 253748 along with the alpha portion.

Posted By: DBlank — 9 Apr 2010 at 5:20am

try:

mid({OBVARC_txt.DESCRIPTION},

(Local StringVar inString := {OBVARC_txt.DESCRIPTION};

Local NumberVar strLen := Length (inString);
Local NumberVar result := -1;
Local NumberVar a := 1;
While a <= strLen And result = -1 Do
While a <= strLen And result = -1 Do
(
   Local StringVar c := inString [a];
   If NOT (c=" " or NumericText (c)) Then
      result := a;
   a := a + 1;
);
result)
)

Posted By: lphenderson — 9 Apr 2010 at 5:27am

Absolutely perfect. I added one more condition for 'NOT' to eliminate a decimal on one row. It seems to have covered all the conditions! I really appreciate you helping me out.