Strip values from string

[ShortyA]

Hi folks.
I am using Crystal Reports XI R2 against an Oracle DB using native driver.
I have a field with values such as 'DD port.1.2.1' and 'DD port.12.3.19'. Note that all of the numerics can be either single digits or double digits so the field length varies. Basically I want to strip the values out so that the value after the first decimal point is placed in a formula, the values after the second decimal point is captured and the same with the third.

Data:
RowID,Value
1,'DD port.1.2.1'
2,'DD port.12.3.19'
Output:
RowID,Shelf,Slot,Port
1,1,2,1
2,12,3,19

Has anyone done this as I have not stripped out values when there are multiple delimiters ?
Many thanks - Alec

 

[IanWaterman]

Provided it is always preceded by port.

@Port

split({yourfield},"port.")[2]

Ian

 

[ShortyA]

Hi Ian.
Thanks for your reply. I receive an error message of "a subscript must be between 1 and the size of the array" when add this to my report. As there will always be 3 different output values (Shelf,Slot,Port) is there some way of fixing the array to be 3 ? Or another way of not generating the error message ?
Alec

 

[IanWaterman]

Not sure what you mean. Where does shelf and slot come from?

Looks like the string "port." is not always present in your field.

Ian

 

[ShortyA]

From a single field value of 'DD port.12.3.19' I want to extract the following:
Column names --Shelf,Slot,Port
Values -- 12,3,19.

Normally I would just use a MID but as the numeric values can be 1 or 2 digits their positions are variable but ALWAYS after a decimal point.

 

[CR85user]

Create 3 formulas and lay them out in any way you prefer.

//{@shelf}
split({yourfield},".")[2]

//{@Slot}
split({yourfield},".")[3]

//{@port}
split({yourfield},".")[4]

'J

CR8.5 / CRXI - Discovering the impossible

 

[ShortyA]

CR85User - fantastic - just what I wanted - thanks!
ShortyA