Comparing values correctly

[Eddie Shipman]

I've got two field values from a database that are for a chart of
accounts and can be entered as numbers (as strings) and
even contain dashes, i.e. 9120-10

Now, I am to validate a range on this data to see that range2 is not
greater than range1.

DataSet.FieldByName('RANGE_END').AsString
DataSet.FieldByName('RANGE_START').AsString

RANGE_START should NEVER be greater than RANGE_END.

However:

9120-01 < 9120-10 compares correctly.
aaa < abc compares correctly.
130 < 1000 DOES NOT compare correctly.

function IsGreater(AValue1, AValue2: String): Boolean;
begin
// AnsiCompareStr does not work either.
Result := CompareStr(AValue2, AValue1) > 0;
end;

What would I use to correctly evaluate this for all these types of
values?

[Kurt Barthelmess [TeamB]]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote:

>9120-01 < 9120-10 compares correctly.
>aaa < abc compares correctly.
>130 < 1000 DOES NOT compare correctly.

As you wrote, these are strings, not numeric values. So they'll
compare the same way any strings do, and '130' is greater than '1000'
just as '130' is greater than '100'. If you want them evaluated as
numbers, convert them to numbers using TryStrToInt. If that returns
False for either value, one or both are not numbers and you can treat
them as strings. If both calls return True, you have the numeric
values and can compare those.

However, if you were expecting '9129-02' to be the numerical
equivalent of '9127', it will not be. In fact '9129-02' < '9128' will
still be False. You'll have to sort that out. Also, you may want to
think about case here for alpha characters: what is the relationship
between 'a' and 'A'?

Good luck.

Kurt

[Remy Lebeau (TeamB)]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote in message
news:xn0f5dmqk...@forums.borland.com...

> 130 < 1000 DOES NOT compare correctly.

That is because you are doing string comparisons, not integer
comparisons. Each character is individually compared until the end of
the shorter string is reached. So, in this case, the second character
of the first string - '3' - is greater than the second character of
the second string - '0' - so the comparison returns False.

> What would I use to correctly evaluate this for all these
> types of values?

Don't compare them as strings. Convert them to their numberic
counterparts ('9120-01' -> 912001, '9120-10' -> 912010, etc) and then
compare those instead.

Gambit

[Remy Lebeau (TeamB)]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote in message
news:xn0f5dmqk...@forums.borland.com...

> I've got two field values from a database that are for a

> chart of accounts and can be entered as numbers (as
> strings) and even contain dashes, i.e. 9120-10

How significant are the dashes? In other words, are '9120-01' and
'9120-10' actually just '01' and '10' within a common group '9120'?
If so, then you will have to write your own comparison code, for
example:

function IsLessThan(const S1, S2: String): Boolean;
var
Num1, Num2: Integer;
Pos1, Pos2: Integer;
begin
Result := False;
if TryStrToInt(S1, Num1) and TryStrToInt(S2, Num2) then begin
Result := Num1 < Num2;
end else
begin
Pos1 := Pos('-', S1);
Pos2 := Pos('-', S2);
if (Pos1 <> 0) and (Pos2 <> 0) then
begin
Num1 := StrToInt(Copy(S1, 1, Pos1-1));
Num2 := StrToInt(Copy(S2, 1, Pos2-1));
if Num1 = Num2 then
begin
Num1 := StrToInt(Copy(S1, Pos1+1, MaxInt));
Num2 := StrToInt(Copy(S2, Pos2+1, MaxInt));
end;
Result := Num1 < Num2;
end else
Result := S1 < S2;
end;
end;

Gambit

[Eddie Shipman]

Kurt Barthelmess [TeamB] wrote:

Oh, I have thought about it and am at a dead-end.

This is for a chart of accounts and it could be anything that
the user enters when he creates the COA. You know accounting types. ;-)

Using TryStrToInt can work but if it fails, I don't want to just take
out dashes, because it could have other punctuation in there, such as
periods. Or it could be all alpha.

There are a lot of options and I was hoping there was one good
comaprison routine that would make this easier. Looks like I have my
work cut out for me.

--

[John Herbster]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote

> This is for a chart of accounts and it could be anything that
> the user enters when he creates the COA. You know accounting
types. ;-)

Back in the early days of computing, libraries and similar
document handlers used to have the program create a
second field from the first just to be used as an index and
key field -- kind of an interface between the user and the
sort routine. --JohnH

[Adem]

Kurt Barthelmess [TeamB] wrote:

>However, if you were expecting '9129-02' to be the numerical
>equivalent of '9127', it will not be. In fact '9129-02' < '9128' will
>still be False. You'll have to sort that out. Also, you may want to
>think about case here for alpha characters: what is the relationship
>between 'a' and 'A'?

Isn't there a simple aritmetic expression evaluator for Delphi?

Did you check any of these?

http://www.efg2.com/Lab/Library/Delphi/MathFunctions/Parsers.htm

[Kurt Barthelmess [TeamB]]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote:

>This is for a chart of accounts and it could be anything that
>the user enters when he creates the COA. You know accounting types. ;-)

Oh, well, that may make it easier. At least in my experience with
those accounting types, they do seem to be fairly consistent in their
formal account numbering conventions, even if their data entry is
sloppy. Otherwise you can't tell an asset from a liability much less
generate a balance sheet. So they might use arbitrary dividers like
dashes, slashes, periods, underscores and the like, and omit leading
zeros, and do all that without any consistency whatsoever, but
generally the number of sub-fields in the account number is
consistent. So then it becomes a matter of normalizing the number
entered into something standard, with that standard being whatever the
CFO says. For example, if she says accounts are to be three
sub-fields, with a max of 3 digits in the first and third and six
digits in the second, you normalize everything to ###-######-###. Does
that help?

>Using TryStrToInt can work but if it fails, I don't want to just take
>out dashes, because it could have other punctuation in there, such as
>periods. Or it could be all alpha.

Right, you accept anything non-numeric as a field separator, throw the
non-numerics away and normalize as above.

>There are a lot of options and I was hoping there was one good
>comaprison routine that would make this easier. Looks like I have my
>work cut out for me.

Have fun<g>.

Kurt

[Eddie Shipman]

Kurt Barthelmess [TeamB] wrote:

<SNIP>
> ... So then it becomes a matter of normalizing the number

> entered into something standard, with that standard being whatever the
> CFO says. For example, if she says accounts are to be three
> sub-fields, with a max of 3 digits in the first and third and six
> digits in the second, you normalize everything to ###-######-###. Does
> that help?
>

Well, we can't dictate what they put in nor the format they put it in.
I have been testing StrCmpLogicalW and it seems to work on the things I
couldn't get to work before:

function StrCmpLogicalW; external shlwapi32 name 'StrCmpLogicalW';

function StrCmpW(psz1, psz2: PAnsiChar): Integer;
begin
Result := StrCmpLogicalW(psz1, psz2);
end;

procedure TForm1.Button1Click(Sender: TObject);
var
a, b, c, d, e, f: String;
x: Integer;
begin
memo1.lines.clear;
a := '9020-01';
b := '9020-10';
c := '130';
d := '1000';
e := 'aaa';
f := 'abc';
// 1 > 2 = 1
// 1 < 2 = -1
// 1 = 2 = 0
x := StrCmpW(Pchar(a), Pchar(b));
if x > 0 then
memo1.lines.add(a + ' is greater than ' + b)
else
memo1.lines.add(a + ' is less than ' + b);

x := StrCmpW(Pchar(c), Pchar(d));
if x > 0 then
memo1.lines.add(c + ' is greater than ' + d)
else
memo1.lines.add(c + ' is less than ' + d);

x := StrCmpW(Pchar(e), Pchar(f));
if x > 0 then
memo1.lines.add(e + ' is greater than ' + f)
else
memo1.lines.add(e + ' is less than ' + f);
end;

Result:
9020-01 is greater than 9020-10
130 is less than 1000
aaa is less than abc

the first one is definately incorrect but I wasn't having a problem
with that one using CompareStr. Using CompareStr, the other middle one
is incorrect.

function IsGreater(a, b: String): Integer;
begin
Result := CompareStr(a, b);
end;

9020-01 is less than 9020-10

130 is greater than 1000

aaa is less than abc

If I remove any punctuation in the string, StrCmpLogicalW will work for
all instance.

function Strip(AStr: String): String;
var
s: String;
i: Integer;
begin
s := AStr;
for i := Length(s) downto 1 do
begin
if not (s[i] in ['0'..'9']) then
Delete(s, i, 1);
end;
if Length(Trim(s)) = 0 then
Result := AStr
else
Result := s;
end;

I really didn't want to do that but looks like that is the only way to
get it done correctly.

[Eddie Shipman]

Adem wrote:

>
> Isn't there a simple aritmetic expression evaluator for Delphi?
>

Do you really think this is some sort of mathematical expression?

--

[Ronaldo Souza]

Eddie Shipman wrote:
>
> If I remove any punctuation in the string, StrCmpLogicalW will work for
> all instance.
>

<SNIP>

>
> I really didn't want to do that but looks like that is the only way to
> get it done correctly.

Another approach would be to assume that whatever is typed, it is in
some high order arithmetic base. For instance, if the allowed input
characters are ['0'..'9','A'..'Z','-'], you could consider the input to
be in base 37. You could then convert them to decimal representation for
comparison. Since this should generate a unique output for a given
input, it could be used as John suggested. The drawback with this
approach is that 'AAA3' would be greater than 'ABC', '0AAA' would be
equal to 'AAA' and so on. If that is not a problem, here is some code:

uses
Math;

const
Base37CharSet = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-';

function Base37ToBase10(AValue : string) : extended;
var
i,len,d : integer;
begin
Result := 0;
AValue := UpperCase(Trim(AValue));
len := length(AValue);
for i := 1 to len do
begin
d := pos(AValue[i],Base37CharSet)-1;
Result := Result+d*Power(37,len-i);
end;
end;

function TForm1.CompareBase37(AValue1,AValue2: string): integer;
//only in TForm1 because of the debug labels. Should be apart.
var
v1,v2 : extended;
begin
v1 := Int(Base37ToBase10(AValue1));
Label1.Caption := FloatToStr(v1);
v2 := Int(Base37ToBase10(AValue2));
Label2.Caption := FloatToStr(v2);
if (v1 > v2)
then Result := 1
else if (v1 < v2)
then Result := -1
else Result := 0;
end;

procedure TForm1.Button1Click(Sender: TObject);
const
ResStr : array[-1..1] of string[3] = (' < ',' = ',' > ');
begin
ShowMessage(Edit1.Text+ResStr[CompareBase37(Edit1.Text,Edit2.Text)]+
Edit2.Text);
end;

HTH,
Ronaldo

[Eddie Shipman]

Ronaldo Souza wrote:

> Eddie Shipman wrote:
> >
> > If I remove any punctuation in the string, StrCmpLogicalW will work
> > for all instance.
> >
> <SNIP>
> >
> > I really didn't want to do that but looks like that is the only way
> > to get it done correctly.
>
>
> Another approach would be to assume that whatever is typed, it is in
> some high order arithmetic base. For instance, if the allowed input
> characters are ['0'..'9','A'..'Z','-'], you could consider the input
> to be in base 37. You could then convert them to decimal
> representation for comparison. Since this should generate a unique
> output for a given input, it could be used as John suggested. The
> drawback with this approach is that 'AAA3' would be greater than
> 'ABC', '0AAA' would be equal to 'AAA' and so on. If that is not a
> problem, here is some code:
>

<SNIP>

Thanks for the code, Ronaldo, but the "delimiters" could be periods,
commas, dashes, slashes, I have no idea what it could be, if anything
at all.

Just computing the sum of the ordinals also wouldn't work:

AAA = 65+65+65=195
ABC = 65+66+67=198

BUT

9120-01 = 57+49+50+48+45+48+49 = 346
9120-10 = 57+49+50+48+45+49+48 = 346

--

[Ronaldo Souza]

Eddie Shipman wrote:
>
> Thanks for the code, Ronaldo, but the "delimiters" could be periods,
> commas, dashes, slashes, I have no idea what it could be, if anything
> at all.

If I understood the problem correctly, all you'd have to do is work on a
higher base. For instance, if '.','/',',' and ';' are valid input chars,
you'd work on base 40, and so on. Remember that dashes, periods, etc
will be treated as normal digits, such as '1' or '9'. All you'd have to
do is have *all* allowed chars in MyBaseCharset:

const
MyBaseCharSet = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-./,;';
// Just add whatever chars are valid inputs... ^^^^

Converting from base 40, we have:

Base40 Decimal
----------------------
AAA 14070
ABC 14109
9120-01 23164679245
9120-10 23164679281

Is a result such as "AAA0 > ABC" a problem?

HTH,
Ronaldo

[Adem]

Eddie Shipman wrote:

Opps. Sorry. I thought I understood the problem. No, I hadn't.

I missed most of the discussion --in the sense of hit&miss.

Especially this bit:

"This is for a chart of accounts and it could be anything
that the user enters when he creates the COA. You know
accounting types. ;-)"

The only thing I can think of is to get hold of all the
different sets variations of patterns and try to apply
some logic to them.

For the kind of free-hand you seem to imply there isn't
much else I can suggest other than this --save using some
sort of neural network stuff which might be way too big a
project in its own right.

[Kurt Barthelmess [TeamB]]

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote:

>Well, we can't dictate what they put in nor the format they put it in.
>I have been testing StrCmpLogicalW and it seems to work on the things I
>couldn't get to work before:

>If I remove any punctuation in the string, StrCmpLogicalW will work for
>all instance.

Most often though, those delimiters are significant in a COA. Just to
throw sand in the gears, consider these account representations:
902-0-123
902-01-23
Are these supposed to be the same? Typically the subfields of the
account number designate a division, department,
asset/liability/income/expense, or purpose, with the number of
sub-fields varying depending on the organization's size and level of
detail the CFO wants. So the delimiters aren't always a '-' or a '.',
but they do have meaning. So the account numbers above would not be
the same; their normalized form would be:
902-000000-123
902-000001-023
How the user enters them can be much more free form as long as there's
some delimiter to separate the subfields. That first account, could be
entered as:
902.0$123
902.000-123
and so on. Or for the industrial strength, touch numeric key padist:
902000000123

Kurt

[Eric W. Carman]

Well, this won't actually answer your question, but it is a method we've
used to avoid this type of problem.

We include on the database row a column "Sequence" (or some such name) and
allow the user - as part of their efforts to maintain the chart of
accounts - to decide what order they want to see things in. <poor example
to follow>Invariably our uses have had a line 10, 20, 30, etc. and decided
they needed to change things around and we end up with 90, 10, 30, 20, etc.
</poor example to follow>

At a minimum it removes the responsibility from you attempting to decide
what random punctuation will sort where - as there will always be an
exception to the rule. When retrieving data for the display, we just sort
by our "Sequence" column.

Best Regards,
Eric

"Eddie Shipman" <mr_delphi...@nospamyahoo.com> wrote in message
news:xn0f5dmqk...@forums.borland.com...

[Eddie Shipman]

Kurt Barthelmess [TeamB] wrote:

Well, here's what we're going with, code from delphipages.com:

Sorry Ronaldo, but this one works, whereby yours didn't.

const
Base32Str = '0123456789ABCDEFGHIJKLMNOPQRSTUV';
Base32 : array [0..31] of char = Base32Str;
Base32Max = 13; //max size req for Int64 Base32 is 13

function IntToBase32(X: Int64; Length: integer = 0): string ;
var
buffer : array[0..Base32Max]of char;
n, len : integer;
begin
Length := Abs(Length);

if Length < 1 then
result := Base32[0]
else
result := StringOfChar(Base32[0], Length);

X := Abs(X);
if X = 0 then
exit;

FillChar(buffer, SizeOf(buffer), Base32[0]);
n := Base32Max +1;
repeat
Dec(n);
buffer[n] := Base32[X mod 32];
X := X div 32;
until (X = 0) or (n < 0);

if n >= 0 then begin
len := Base32Max +1 -n;

if Length > len then begin
result := StringOfChar(Base32[0], Length);
Move(buffer[n], result[Length -len +1], len);
end else begin
SetLength(result, len);
Move(buffer[n], result[1], len);
end;
end;
end;

function StrCmpLogical(const s1, s2: string): integer;

function GetIndexedStr(const s: string): string;
var
buffer : string;
i, x : integer;
Strs : TStringList;
begin
result := '';
if s <> '' then begin
Strs := TStringList.Create;
try
SetLength(buffer, Length(s) *2);
x := 0;
for i := 1 to Length(s) do begin
if s[i] in ['0'..'9'] then begin
if (x > 0) and not(buffer[x] in [#10, '0'..'9']) then
begin
Inc(x);
buffer[x] := #10;
end;
end else begin
if (x > 0) and (buffer[x] in ['0'..'9']) then begin
Inc(x);
buffer[x] := #10;
end;
end;

Inc(x);
buffer[x] := s[i];
end;

SetLength(buffer, x);
Strs.Text := buffer;

if Strs.Count > 0 then
for i := 0 to Strs.Count -1 do begin
if TryStrToInt(Strs[i], x) then
//max size of int64 in Base32 is 13
Strs[i] := IntToBase32(x, 13);
end;

result := Strs.Text;
finally
Strs.Free;
end;
end;
end;

begin
result := AnsiCompareStr( GetIndexedStr(s1), GetIndexedStr(s2) );
end;

procedure TForm1.Button1Click(Sender: TObject);
var
a, b: String;
x: Integer;
begin
memo1.lines.clear;
a := '9120-08';
b := '9120-01';
x := StrCmpLogical(a, b);
if x > 0 then
Memo1.Lines.Add(a + ' is greater than ' + b)
else
Memo1.Lines.Add(a + ' is less than ' + b);
a := '130';
b := '1000';
x := StrCmpLogical(a, b);
if x > 0 then
Memo1.Lines.Add(a + ' is greater than ' + b)
else
Memo1.Lines.Add(a + ' is less than ' + b);
a := 'aab';
b := 'aaac';
x := StrCmpLogical(a, b);
if x > 0 then
Memo1.Lines.Add(a + ' is greater than ' + b)
else
Memo1.Lines.Add(a + ' is less than ' + b);
end;

--

[Dr J R Stockton]

In borland.public.delphi.language.delphi.general message <4630c3d3$1@new
sgroups.borland.com>, Thu, 26 Apr 2007 10:21:19, John Herbster <herb-
sci1_AT_sbcglobal.net@?.?.invalid> posted:

>Back in the early days of computing, libraries and similar
>document handlers used to have the program create a
>second field from the first just to be used as an index and
>key field -- kind of an interface between the user and the
>sort routine.

IMHO, they still should do so.

Sorting N items requires, at best, something like O(NlogN) comparison
operations, whereas computing a set of comparison-friendly sort keys
needs only O(N). Therefore, it pays to simplify the actual comparisons.

It's not necessarily obvious to all that the data to be sorted should be
addressed by pointers or indexes, so that if comparison calls for a swap
the data items themselves do not need to be moved, just little
pointers/indexes. Using, for each datum, a record of pointer-to-datum &
pointer-to-key obviously encourages good practice.

None of the above is necessarily invariably true, but this sentence is
probably reliable.

--
(c) John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Delphi 3? Turnpike 6.05
<URL:http://www.merlyn.demon.co.uk/> TP/BP/Delphi/&c., FAQqy topics & links;
<URL:http://www.bancoems.com/CompLangPascalDelphiMisc-MiniFAQ.htm> clpdmFAQ;
<URL:http://www.borland.com/newsgroups/guide.html> news:borland.* Guidelines

[Iain Macmillan]

In article <xn0f5fy1n...@forums.borland.com>, "Adem"
<adem...@excite.com> wrote:

>>Do you really think this is some sort of mathematical expression?

Kurt raised the idea that it might be a subtraction in the very first reply,
and until he wrote the above Eddie hadn't stated that it wasn't.

> Opps. Sorry.
So I didn't know whether it was or not, either till now.

> I thought I understood the problem. No, I hadn't.

I'm not sure that Eddie does either.. if he does he hasn't explained it very
well..
He seems to know what the results of the comparison should be (because he
said some were wrong) but its not clear to me how he determines which should
be considered bigger.

>
> I missed most of the discussion --in the sense of hit&miss.
>
> Especially this bit:
>
> "This is for a chart of accounts and it could be anything
> that the user enters when he creates the COA. You know
> accounting types. ;-)"

It seems that what these symbols mean is ill-defined, and until you know
what they mean, how can you determine whether one is greater or less than
another - what does it *mean* to be bigger?
..Its like asking whether apples are fruitier than oranges..