469,304 Members | 2,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

How test for all digits

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?
Nov 15 '05 #1
9 22329
Bob Stearns wrote:
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...
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 16 '05 #2
CASE
WHEN LENGTH(RTRIM(TRANSLATE(test_str, '', '0123456789'))) = 0 THEN
'All digits'
ELSE 'No'
END

Nov 16 '05 #3
If you are feeling particularly evil, you could just INTEGER() the
argument and trap error 420.

B.

Nov 16 '05 #4
Knut Stolze wrote:
Bob Stearns wrote:

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

How slow is this going to be as a CHECK constraint?
Nov 16 '05 #5
Tonkuma wrote:
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.
Nov 16 '05 #6
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.

Nov 16 '05 #7
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.

Nov 17 '05 #8
Tonkuma wrote:
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.

Thank you.
Nov 17 '05 #9
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!

Nov 17 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Nicolae Fieraru | last post: by
6 posts views Thread by WindAndWaves | last post: by
14 posts views Thread by Christopher Benson-Manica | last post: by
2 posts views Thread by Magix | last post: by
27 posts views Thread by Luke Wu | last post: by
4 posts views Thread by Anoop | last post: by
5 posts views Thread by Dr. Pastor | last post: by
17 posts views Thread by =?Utf-8?B?TWljaGVsIFBvc3NldGggW01DUF0=?= | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.