More on converting a currency string to a real (introducing STRINDEX)

As an astute comment pointed out, the code that I presented the other day for converting a currency string (like $1,234.56) to a real would have problems if the string represented an amount greater than one million. I was going to write that Priority has no STRPOS function; if it had one, then locating the commas and removing them would be simpler than my laborious code. 

I have just discovered that whilst there is no STRPOS function, there is a function called STRINDEX that is more or less the same. So if there was a string like '$1,234,567.89', it could be turned into a string that can be turned into a real with the following code.

SUB 850;
/* :ASTRING will be something like '$1,234,567.89';
*/
:ASTRING = SUBSTR (:ASTRING, 2, 24); /* Lose the leading dollar */
:AREAL = '';
:RESULT = 0E9;
:POS = 0;
LABEL 851;
SELECT STRINDEX (:ASTRING, ',', 1) INTO :POS FROM DUMMY;
GOTO 852 WHERE :POS = 0;
:AREAL = STRCAT (:AREAL, SUBSTR (:ASTRING, 1, :POS - 1));
:ASTRING = SUBSTR (:ASTRING, :POS + 1, 24);
LOOP 851;
LABEL 852;
:AREAL = STRCAT (:AREAL, :ASTRING);
:RESULT = ATOR (:AREAL);
RETURN;
  

Note that the $ sign is liable to be converted into the procedure's name (see here). Fortunately, in my case, the currency sign is ₪ which is ignored by the preprocessor.

Now that I know about the existance of the function STRINDEX, I am liable to use it whenever necessary. I doubt that I am going to update existing code but I will use it in the future. 

It turns out that STRINDEX exists only from version 21 onwards - and of course, the client who needs this code is running version 20.

More on the undocumented function ATOR

A year and a half ago, I wrote about the undocumented function ATOR that takes a string and turns it into a real number with a decimal point. A couple of days ago, I was dismayed to learn that the function wasn't working properly with an Excel file that someone had sent intended as input for an interface that uses ATOR. After looking at the file for a while, I realised that every line had four fields in the following format $1,234.56, whereas previously the fields had held 1234.56. ATOR chokes on this new input and so I had to write a new function. First, though, some examples:

  SELECT ATOR ('$1,061.25') FROM DUMMY FORMAT; >> error
  SELECT ATOR ('1,061.25') FROM DUMMY FORMAT; >> 0.010000  Why?
  SELECT ATOR ('1061.25') FROM DUMMY FORMAT; >> 1061.250000
  

So in order to parse the field (that was being passed as a string), first I had to strip off the dollar sign. It wasn't clear at first whether the dollar sign was at the beginning or end of the string, but it turned out to be the beginning. This was easy to do. Then I looked for the decimal point: anything before this is a number and should be read as is, the two digits after this are the digits after the decimal point and should be multiplied by 0.01. 

But this didn't work properly, either. It seems that the comma was causing problems, so first I had to find where the comma was - if at all - and make allowance for this. The final code is as follows

SUB 850;
/* AREAL is something like $ 12,345.67 */
:RTMP = 0.0;
:AREAL = STRIND (:AREAL, 2, 10); /* lose the $ */
SELECT STRPIECE (:AREAL, '.', 2, 1) INTO :TMP FROM DUMMY;
:RTMP = ATOI (STRIND (:TMP, 1, 2)) * 0.01; /* fractional part */
SELECT STRPIECE (:AREAL, '.', 1, 1) INTO :TMP FROM DUMMY;
SELECT STRPIECE (:TMP, ',', 1, 1) INTO :THOUSAND FROM DUMMY;
GOTO 851 WHERE :THOUSAND = :TMP;
:RTMP = :RTMP + (ATOI (:THOUSAND) * 1000.0) +
ATOI (STRPIECE (:TMP, ',', 2, 1));
GOTO 852;
LABEL 851;
:RTMP = :RTMP + ATOI (:THOUSAND);
LABEL 852;
RETURN;
  

This isn't something that I'm proud of, but at least it works. Before the subroutine is called, there is a check to see whether the field holds an 'ordinary' real like 1234.56 - this is accomplished with the ISFLOAT function, and if this returns 1, then ATOR is used on the field. Otherwise the subroutine is called.