In String Search and Trim

[robbclaxton]

I would like to search a string for a keyword or phrase and subsequently strip the keyword and the proceeding characters from the string.

for example
Tank/Sensor-number: 04 - Questionable Deiveries:
01/02/2011 02:00:00 - Ticketed=14400.0 - Measured=11655.0

Find 'Ticketed=' and return 'Ticketed=14400.0'
Find 'Measured=' and return 'Measured=11655.0'

Equally I would like to strip the date record but due to the spaces I wondered whether I could find the start and end of the date.

Any help would be really appreciated.

 

[IanWaterman]

If data is split by " - " you can use the split function

@date

DTSToDate(split({yourfield}, " - ")[1]

@Ticketed
split({yourfield}, " - ")[2]

@Measured
split({yourfield}, " - ")[3]

Ian

 

[lbass]

//{@Ticketed}:
mid({table.string}, instr({table.string},"Ticketed"),16)

//{@Measured}:
mid({table.string}, instr({table.string},"Measured"),16)

The above assumes that the number is always the same length and that there are no spaces within the segment.

For the date, you could use the following if the date is always the first part of the second line:

stringvar x := {table.string};
stringvar y := split(x, chr(13))[2];
stringvar z := split(y," - ")[1];
datetime(date(val(mid(z,7,4)),val(mid(z,4,2)),val(mid(z,1,2))),time(mid(z,12)));

-LB

 

[robbclaxton]

I thank you both for your help I have one another question should I get the following scenario..how do I get around it?

Tank/Sensor-number: 06 - Questionable Deiveries:
01/13/2011 02:00:00 - Ticketed=14802.0 - Measured=-14859.0
01/12/2011 02:19:00 - Ticketed=14703.0 - Measured=-14767.0

 

[lbass]

Are there always returns after what you are showing as your first line and after multiple entries as shown? Also, you seem to have changed the length of the "measured" numbers--is this intentional?
Please clarify what is always the same and what can change.

-LB

 

[robbclaxton]

the numbers after the measured could potentially be negative, but this indicates a recording error.

It is possible that there will be multiple lines of data in the string, and I have no control on this output.

I guess we need to split somehow using the delimters in place, Start of Line, "-" and end of line. But it sounds easier said than done .

Many Thanks

 

[lbass]

Again--

Are there always returns after what you are showing as your first line and after multiple entries as shown?

-LB