Extracting leading numeric characters from string 1

[midearth]

Using Crystal XI and Oracle DB.

I have a database field containing a set of values contained within {} which I've already been able to remove in most instances using a formula I found on Tek-Tips (Thanks lbass!!) for the display output. The formula which I modified slightly was:

stringvar array x := split({TABLE.FIELD},"}");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
y := y + trim(extractstring(x,"","{")));

However, this sometimes leaves me with a string display starting with { followed by a series of numeric characters anywhere from 1 - 10 characters in length. What I would like is to trim the remaining part so that the resulting value would display properly. I can do this by continuing to declare stringvar and replacing for each instance I find but would like a more permanent solution.

Current formula output is something like: {2004042214HEIGHT*15.5/LENGTH

with the prefered output of: HEIGHT*15.5/LENGTH

BTW: Original output without formating is something like:

{this.2004042214.1.HEIGHT}*15.5/{this.1918.3.LENGTH}

I used some replace functions prior to the above formula to replace the "this." with "" and the ".1." or ".3." portions with a "}". In combination with the above formula, it works great unless the leading character is {.

 

[lbass]

How about showing a selection of original data (without any adjustments) that shows the range of original data and the corresponding desired output.

-LB

 

[midearth]

Hi lbass! Thanks for your quick reply.

Original data is very variable. I'm using the "Display String" custom formula formatting in the Format Field trying to do this. Examples of original data would be:

Case 1. {this.2004042214.1.HEIGHT}*15.5/{this.1918.3.LENGTH}

Case 2. ({this.2004042214.1.HEIGHT}*15.5)/({this.1918.3.LENGTH}*100)

Case 3. (({this.2004042214.1.HEIGHT}-{this.1918.3.LENGTH})-1)*{this.889701.11.WIDTH}

Desired output:

Case 1: HEIGHT*15.5/LENGTH
Case 2: (HEIGHT*15.5)/(LENGTH*100)
Case 3: ((HEIGHT-LENGTH)-1)*WIDTH

The database is actually set up to use automatic calculations so everything contained within the {} is part of the SQL statement in the background. However, when the database is down for maintenance, etc. the users like to have a printout of the calculations for entering the data later on. I'm trying to write the report so it just displays what's used for the calculation without all of the "extra" SQL portions. What I have, thanks to you, works great with my modification attempts as long as the original data does not use { as the starting character in the string.

 

[midearth]

While still not perfect, I did figure out a way to do this without having to have multiple "replace" statements later on for the different variations by replacing the leading "{" character in the string by combining with another leading character not being used anywhere else then removing it.

stringvar a:=replace({TABLE.FIELD},"{this.","\{");
stringvar array x := split(a,"}");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
y := y + trim(extractstring(x,"","{")));
stringvar z:=replace(y,"\","");
z