Print Page | Close Window

substring formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9642
Printed Date: 02 May 2024 at 10:53am


Topic: substring formula
Posted By: lphenderson
Subject: substring formula
Date Posted: 08 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?


-------------
lphender


Replies:
Posted By: kevlray
Date Posted: 08 Apr 2010 at 8:09am
There is no substring in Crystal, use the mid function.

Posted By: lphenderson
Date Posted: 08 Apr 2010 at 8:19am
Don't you have to use exact positions in the string with the Mid function?
Mid (, )


-------------
lphender

Posted By: lphenderson
Date Posted: 08 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'?

-------------
lphender

Posted By: kevlray
Date Posted: 08 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
Date Posted: 08 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)
)

Posted By: lphenderson
Date Posted: 09 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.

-------------
lphender

Posted By: DBlank
Date Posted: 09 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)
)

Posted By: lphenderson
Date Posted: 09 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!


-------------
lphender

Posted By: lphenderson
Date Posted: 09 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?

-------------
lphender

Posted By: DBlank
Date Posted: 09 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
Date Posted: 09 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.


-------------
lphender

Posted By: DBlank
Date Posted: 09 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
Date Posted: 09 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.

-------------
lphender


Print Page | Close Window