Split function question 1

[DrHabi]

I have a field in my database that has dollar amounts seperated by a blank space such as '100.00 100.00 2.25 225.00 79.00 35.1 146.00 0.79 64.89'
I currently use this formula to extract the numbers out of this field into sperate qtuantities but find it to be use too much of the processors resources.
If Not NumericText (Split ({PlnDetail.qtys}, CHR(9), -1)[1]) then 0 else
ToNumber (Split ({PlnDetail.qtys}, CHR(9), -1)[1])

Do anyone know of a formula that could be more efficient.
Thanks

 

[bdreed35]

I have never had a problem with the split function. Perhaps it has to do with another part of the report, like the record selection.

If you really don't want to use the split function, you can use the Instr function:


If Not NumericText (left({table.field},instr({table.field}, " "))) then
0
else
ToNumber (left({table.field},instr({table.field}, " ")))


~Brian

 

[DrHabi]

I have to use multiple formulas for extracting out each quantity so I don't believe this would work such as

If Not NumericText (Split ({PlnDetail.qtys}, CHR(9), -1)[4]) then 0 else
ToNumber (Split ({PlnDetail.qtys}, CHR(9), -1)[4])


If Not NumericText (Split ({PlnDetail.qtys}, CHR(9), -1)[5]) then 0 else
ToNumber (Split ({PlnDetail.qtys}, CHR(9), -1)[5])

 

[Turkbear]

Hi,
Try 'reversing' the test...sometimes a NOT takes longer to evaluate..

If  NumericText (Split ({PlnDetail.qtys}, CHR(9), -1)[5]) then ToNumber (Split ({PlnDetail.qtys}, CHR(9), -1)[5]) else
  0

hth,
[profifle]

 

[lbass]

If you have 8.5 or above, you should be able to use the replace function up to the maximum number of spaces between two elements in the string, as in:

tonumber(split(replace(replace(replace(replace({table.string}," "," "),
" "," ")," "," ")," "," ")," ")[1]) //for the first element

This doesn't work in 8.0 because of a glitch in the replace function for this version, but in theory it should work in higher versions. I think there is a way to make it work in 8.0, but I can't remember what the trick is.

-LB

 

[DrHabi]

Thanks for your ideas I think switching the formula around increased the speed by remiving the "not