An easier way to trim out of a string?? 1

[eo]

Hi

CR10, SQL

I have a field, for example 6/125/100/142500/LT
Each section between the '/'s needs to be trimmed out as a field on its own.
The only way I know how to do this is via a 3 step process for EACH section...
1) Create a marker in the string: @Marker

if instr ({field], '/') >0
then instr ({field], '/') -1

2) Trim the section out: @Trim

Left ({field}, @Marker)

3) Create a new string for the next step: @String

Mid (field}, @Marker)

The new string is now 125/100/142500/LT, and the whole process must be repeated to romove the next section, and so on.

Surely ther must be an easier way?

EO
Hertfordshire, England

 

[Madawc]

Try Split({field}, "/"), which will put the elements into an array.

Madawc Williams (East Anglia, UK)

 

[eo]

Hi

An error suggests that "the result of the formula cannot ba an array", and even if I manage to create an array, how do I split the various sections intotheir own entities. Is there not perhaps SQL coding that would solve this in one go?

EO
Hertfordshire, England

 

[mbarron]

Use Madawc's formula to create the array. The "" is so that the formula doesn't result in an array.

Formula 1:
whileprintingrecords;
stringvar array values:=Split({field}, "/");
""

Formula 2 (3,4, etc ) used to display each position within the array:
whileprintingrecords;
stringvar array values;
values[1] //increment for each member

Mike

 

[eo]

An error in formula 2 (3, 4, etc) states that,
"A subscript must be between 1 and the size of the array" with the section values[1] highlited.

EO
Hertfordshire, England

 

[eo]

I should probably also add that formula 1 results in no results on the report?

EO
Hertfordshire, England

 

[mbarron]

Formula one is designed to show nothing on the canvas. Otherwise youll get the ""the result of the formula cannot be an array" error.

In formula 2 (etc) change the evaluation time part and add a check to see if there is somehting in the position you are displaying.

evaluateafter({@array.formula})
stringvar array values;
if ubound(values)>=1 then
values[1] else
""


For formula one change it to this:
whileprintingrecords;
stringvar array values:=[""];
values:=Split({field}, "/");
""


Mike

 

[eo]

Hi
The solution supplied above works well, but there is one small anaomily and I am not sure how to overcome. Variations on the field is:
125/100/142500/LT
125/100/142500/
125/100/142500

I can obtain the sections, but it errors on the last section due to (what I expect is) the lack of the /

Any ideas how to compensate for this.



EO
Hertfordshire, England

 

[mbarron]

Are you incrementing both of the 1's

evaluateafter({@array.formula})
stringvar array values;
if ubound(values)>=1 then
values[1] else
""

Mike

 

[eo]

No I did not.


Thanks a million!!!

EO
Hertfordshire, England

 

[mbarron]

Mike