sub() function with negative occurance not always working as expected

[Ken Sproul]

I'm not sure the sub() function is working correctly with a negative occurrence argument.  Tried it in versions 12.50 and 15.10.

-}?sub("#####.000","###",",###",-1)
,#####.000

Shouldn't it return this?
##,###.000

Out of curiosity I tried this which works:
-}?sub("#####.000","#","0",-1)
####0.000

But this didn't:
-}?sub("#####.000","##","0",-1)
##0#.000

I would have expected ###0.000.

Is there an explanation or is this a bug?

[Mike King]

Internally the SUB breaks down the input into multiple segments which match what you are scanning for. 

If you look at "#####.00" and determine the number of "###" matches there is really only one occurrence of ### since we continue to scan only AFTER each occurrence.  This attempts to avoid confusion.

Consider if you wanted to find occurrence 2 as in SUB("#####.00","###","XXX",2).  If we didn't exclude prior occurrences of the full search string this would lead to "#XXX#.00" yet  that would be wrong since there no longer would be a first occurrence of "###" so replacing the 2 thru 4th hashes weren't truly the second occurrence - and in fact there isn't a second occurrence -- only a single occurrence of "###".

When replacing the last occurrence we scan the full string finding all occurrences then take the last occurrence found.

Another way to look at this is ask the question -- How many occurrence of "###" exist in "#####.00" -- the answer is only one since if you issued SUB("#####.00","###","XXX") it would replace all occurrences which would only be one occurrence.

[Ken Sproul]

Ahh, the text search is always from left to right and the occurrence is simply counting left to right or right to left.  This is different from the way the pos() function operates so it would be helpful to note this in the reference manual.  It would be nice to have a form of the sub() function that scans the text from right to left.

[Devon Austen]

We will fix the documentation for SUB() in a future release. Thanks for bringing this to our attention.

We can't change the way SUB() works as it would break existing code. You can however accomplish what you want fairly easily using POS()

Code: [Select]

str$="#####.000"
search$="###"
replace$=",###"
startpos=POS(search$=str$,-1)
lensearch=LEN(search$)
str$=MID(str$,1,startpos-1)+replace$+MID(str$,startpos+lensearch)


[Mike King]

Ken

In your example you look to be trying to insert a comma in the thousands position.  When an occurrence is specified the SUB will only do a single occurrence but I suspect you want a generic routine that will replace/insert all.  (e.g. #####.000 -> ##,###.00 and #######.000 -> #,###,###.00).  If so the SUB is not the way to go.

This logic however might get you what you want:

LET o=POS("."=x$,-1)
IF o>4 AND MID(x$,o-4,1)="#" THEN LET x$=x$(1,o-4)+","+x$(o-3); LET o=o-3; GOTO *SAME

Assuming X$ has the format such as #####.00 or #########.00 or even ######0.00 

[Ken Sproul]

Thanks for the explanation and the ideas.  I ended up creating the following function:

def fn_mask_commas$(local x$)
x$=stp(x$,3,","); local s=msk(x$,"##*0*"),e=s+msl-3,p
if s<e then for p=e to s step -3; x$=x$(1,p-1)+","+x$(p); next p
return x$
end def