FWiW, that expression is only sufficient to determine which character
field values do not represent valid _positive_ integers; i.e. valid
character representation of negative integer values would be selected by
that predicate. If the character field includes a left-negative sign for a
valid integer value, the [implicit] cast to
numeric would occur without error.
... and you forgot that sometimes decimal dots and/or decimal commas could
be used and a plus or minus sign could be used on the right or left hand,
instead of a minus sign the numeric value can be enclosed within
parenthesis. Sometimes also a dot or a comma is used to separate the digits
on the left hand of the decimal comma/dot (for example 1.234.567,89 or
1,234,567.89).
You'll see all those presentations in the same column/field if you get an
SAP interface (at least I saw it in that one I got).
... and that's why I have an RPG function that checks all those
representations. An other RPG function converts the checked values into a
numeric value. Both functions are registered as UDFs and used in SQL.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Friday, 04. June 2010 20:21
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: AW: Is this me or another SQL bug?
On 03-Jun-2010 06:12, Birgitta Hauser wrote:
<<SNIP>>
To test for invalid numeric values you may run the following
query:
Select CharacterField
From Table
Where
Length(Trim(
Translate(CharacterField, ' ', '1234567890')
) ) > 0
FWiW, that expression is only sufficient to determine which
character field values do not represent valid _positive_ integers;
i.e. valid character representation of negative integer values would
be selected by that predicate. If the character field includes a
left-negative sign for a valid integer value, the [implicit] cast to
numeric would occur without error.
I did not test, but I expect that...
The following expression would strip the minus sign, but the
predicate would not be able to detect the case where spaces occur
between the minus sign and\or the digits:
where
translate( strip(trim(CharacterField), L, '-')
, ' '
, '1234567890') <> ''
To solve the issue with embedded blanks, the Length() could be
introduced in the above expression just as in the quoted message, or
any remaining blanks could be converted to a non-blank [e.g. an
asterisk]:
where
translate( strip(trim(CharacterField), L, '-')
, ' *'
, '1234567890 ') <> ''
Regards, Chuck
midrange.com
