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? 9 23468
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
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: 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?
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.
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: 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.
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Nicolae Fieraru |
last post by:
Hi All,
I have some javascript code and I try to filter out any characters (which
are not digits or delete) from an input element in a form.
Here is the code:
<!DOCTYPE HTML PUBLIC...
|
by: WindAndWaves |
last post by:
Hi Folks
I have inhereted a script that I understand reasonably well, I just do not
understand
!/^\d+$/.test(el.value)
what the hell does that mean?
Below is the script (there are really...
|
by: Christopher Benson-Manica |
last post by:
I'm trying to check whether a string is all digits. This part is
easy:
function allDigits( str ) {
var foo=str.split( '' ); // better than charAt()?
for( var idx=0; idx < foo.length; idx++ ) {...
|
by: Magix |
last post by:
Hi,
I would like to add the char of string in hex (WORD format) to have the
checksum (in WORD).
E.g char *p =" Test"
so in hex (WORD), it will be
0x0054 (T)
0x0065 (e)
|
by: Luke Wu |
last post by:
Is there a C function that returns the number of digits in an input
int/long?
example:
numdigits(123) returns 3
numdigits(1232132) returns 7
|
by: Astra |
last post by:
Hi All
Could somebody please confirm that if I change my JS expression test from:
if (!(/^*$/.test(document.form1.fred.value)))
to
if (!(/^*$/.test(document.form1.fred.value)))
|
by: Anoop |
last post by:
Hi All
I am getting two different outputs when i do an operation using
string.digits and test.isdigit(). Is there any difference between the
two. I have given the sample program and the output
...
|
by: Dr. Pastor |
last post by:
In the following code I would like to ascertain
that x has/is a number. What the simplest TEST should be?
(Could not find good example yet.)
---
x=raw_input('\nType a number from 1 to 20')
if...
|
by: =?Utf-8?B?TWljaGVsIFBvc3NldGggW01DUF0=?= |
last post by:
Hello ,
Does someone knows a simple way of how to get the nr of fraction digits ?
example :
1.23 would give a result of 2
1.234 would give a result of 3
Yes
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |