| Author |
Message |
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
   Topic: substring formula
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
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 08 Apr 2010 at 8:09am |
|
There is no substring in Crystal, use the mid function.
|
|
IP Logged |
|
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
Posted: 08 Apr 2010 at 8:19am |
Don't you have to use exact positions in the string with the Mid function?
Mid (, )
|
|
lphender
|
|
IP Logged |
|
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
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
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)
)
Edited by DBlank - 08 Apr 2010 at 10:00am
|
|
IP Logged |
|
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)
)
Edited by DBlank - 09 Apr 2010 at 4:34am
|
|
IP Logged |
|
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
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
|
|
IP Logged |
|
lphenderson
Newbie
Joined: 08 Apr 2010
Online Status: Offline
Posts: 8
|
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
|
|
IP Logged |
|
|
|
