How test for all digits

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 15 Nov 2005 23:02 GMT

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?

Reply to this Message

Knut Stolze - 16 Nov 2005 07:35 GMT

> 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?

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...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Tonkuma - 16 Nov 2005 13:31 GMT

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

Reply to this Message

Bob Stearns - 16 Nov 2005 18:56 GMT

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

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.

Reply to this Message

Tonkuma - 17 Nov 2005 16:00 GMT

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.

Reply to this Message

Bob Stearns - 17 Nov 2005 21:21 GMT

> I did one mistake. If string includ blanks, they will be interpreted
> digits in my former example.
[quoted text clipped - 24 lines]
>
> 5 record(s) selected.

Thank you.

Reply to this Message

Bob Stearns - 16 Nov 2005 18:47 GMT

>>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.
[quoted text clipped - 10 lines]
>
> Or you use a loop, SUBSTR and BETWEEN...

How slow is this going to be as a CHECK constraint?

Reply to this Message

Brian Tkatch - 16 Nov 2005 20:02 GMT

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.

Reply to this Message

Brian Tkatch - 16 Nov 2005 14:57 GMT

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

B.

Reply to this Message

2803stan@gmail.com - 17 Nov 2005 22:49 GMT

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!

Reply to this Message