I can see no function which tests its argument to be all digits and I
see no easy way to compose such a function from translate, replace, etc.
I must be missing something simple, right?

Bob Stearns wrote:
[color=blue]
> I can see no function which tests its argument to be all digits and I
> see no easy way to compose such a function from translate, replace, etc.
> I must be missing something simple, right?[/color]

Something like this should do the trick:

CASE
WHEN LENGTH(REPLACE(REPLACE(...(REPLACE(str, '0', ''), '1', ''), ...),
'9', '')) > 0
THEN 0
ELSE 1
END

Or you use a loop, SUBSTR and BETWEEN...


--
Knut Stolze
DB2 Information Integration Development
IBM Germany

CASE
WHEN LENGTH(RTRIM(TRANSLATE(test_str, '', '0123456789'))) = 0 THEN
'All digits'
ELSE 'No'
END

If you are feeling particularly evil, you could just INTEGER() the
argument and trap error 420.

B.

Knut Stolze wrote:
[color=blue]
> Bob Stearns wrote:
>
>[color=green]
>>I can see no function which tests its argument to be all digits and I
>>see no easy way to compose such a function from translate, replace, etc.
>>I must be missing something simple, right?[/color]
>
>
> Something like this should do the trick:
>
> CASE
> WHEN LENGTH(REPLACE(REPLACE(...(REPLACE(str, '0', ''), '1', ''), ...),
> '9', '')) > 0
> THEN 0
> ELSE 1
> END
>
> Or you use a loop, SUBSTR and BETWEEN...
>
>[/color]
How slow is this going to be as a CHECK constraint?

Tonkuma wrote:
[color=blue]
> CASE
> WHEN LENGTH(RTRIM(TRANSLATE(test_str, '', '0123456789'))) = 0 THEN
> 'All digits'
> ELSE 'No'
> END
>[/color]
It was not clear from the docs what would happen with an empty string as
the second argument to TRANSLATE. Consider the following passage from
SQL Reference Vol 1:

pad-char-exp
Is a single character that will be used to pad the to-string-exp if the
to-string-exp is shorter than the from-string-exp. The pad-char-exp must
have a length attribute of one, or an error is returned. If not present,
it will be taken to be a single-byte blank.

My interpretation of that is that an empty string as second argument
will be treated as a string of blanks as long as the third argument, so
the example would never work.

An obvious note is, there is a built-in CONSTRAINT to make sure that
data is numeric, it's the data type of INTEGER (or its ilk). What is a
data type other than a CONSTRAINT?

Also, if the data is guaranteed to be alphanumeric, SOUNDEX() can be
used. Being SOUNDEX() will always return the first letter for the first
character (in the four character response) and Z if no alphabetic
characters exist, two checks can be made.

CHECK(SOUNDEX(Text) = 'Z000' AND SOUNDEX(Text || 'A') = 'A000')

B.

I did one mistake. If string includ blanks, they will be interpreted
digits in my former example.
Here is my corrected version
------------------------- Commands Entered -------------------------
SELECT test_str
, CASE
WHEN LENGTH(RTRIM(TRANSLATE(test_str, '*', ' 0123456789')))
= 0 THEN
'All digits'
ELSE 'No'
END
FROM TABLE(VALUES '9876012345'
, '20051118'
, 'ABCDEFG'
, '2005-11-18'
, '12345 67890'
) Q (test_str);
--------------------------------------------------------------------

TEST_STR 2
----------- ----------
9876012345 All digits
20051118 All digits
ABCDEFG No
2005-11-18 No
12345 67890 No

5 record(s) selected.

Tonkuma wrote:[color=blue]
> I did one mistake. If string includ blanks, they will be interpreted
> digits in my former example.
> Here is my corrected version
> ------------------------- Commands Entered -------------------------
> SELECT test_str
> , CASE
> WHEN LENGTH(RTRIM(TRANSLATE(test_str, '*', ' 0123456789')))
> = 0 THEN
> 'All digits'
> ELSE 'No'
> END
> FROM TABLE(VALUES '9876012345'
> , '20051118'
> , 'ABCDEFG'
> , '2005-11-18'
> , '12345 67890'
> ) Q (test_str);
> --------------------------------------------------------------------
>
> TEST_STR 2
> ----------- ----------
> 9876012345 All digits
> 20051118 All digits
> ABCDEFG No
> 2005-11-18 No
> 12345 67890 No
>
> 5 record(s) selected.
>[/color]
Thank you.

Depending on when you want to do the checking, ie, if you absolutely
don't want anything other than an integer to be stored in the database,
and then not worry about what comes out, here's a solution in VB:
____________

Public Function IsInteger(strNumInAsText As String) As Boolean ' For
either Integer or Long; No "," or "."
' There is no builtin ISINT function in VB. ISNUMERIC accepts commas
and decimal points (periods), which may be misplaced.

Dim lThisLong As Long
Dim lTextLength As Long
Dim iTestDigit As Integer
Dim lThisInt As Long

On Error Resume Next

IsInteger = False ' Initialize.

If Not IsNumeric(strNumInAsText) Then GoTo PROC_EXIT ' First
screen.

' It's "Numeric," but is it a legal integer according to our
definition? IE, Nothing but numbers?

lTextLength = Len(strNumInAsText)
If lTextLength > 10 Then GoTo PROC_EXIT '
Longer than the longest legal LONG(2,147,483,647).

For lThisInt = 1 To lTextLength
iTestDigit = Asc(Mid(strNumInAsText, lThisLong, 1))
If iTestDigit < 48 Or iTestDigit > 57 Then GoTo PROC_EXIT
' "0" is ASCII Chr(48) and "9" is Chr(57).
Next lThisInt

IsInteger = True

PROC_EXIT:
Exit Function

End Function
______________
You can change LONG to BIGINT if appropriate.

Yes, this looks like overkill, but when quick reading is more important
than writing quickly, it's the best way!