isdigit ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

E.Bartosiewicz@gmail.com - 20 Dec 2007 13:17 GMT

How can I check if a char/string is a digit/number in an SQL UDF?

Reply to this Message

Tonkuma - 20 Dec 2007 13:39 GMT

> How can I check if a char/string is a digit/number in an SQL UDF?

If all characters are digit,
TRANSLATE(char_str,'*',' 0123456789') = ''
If leading/trailing blanks are allowed,
TRANSLATE(LTRIM(RTRIM(char_str)),'*',' 0123456789') = ''

Reply to this Message

Knut Stolze - 20 Dec 2007 17:27 GMT

>> How can I check if a char/string is a digit/number in an SQL UDF?
> If all characters are digit,
> TRANSLATE(char_str,'*',' 0123456789') = ''
> If leading/trailing blanks are allowed,
> TRANSLATE(LTRIM(RTRIM(char_str)),'*',' 0123456789') = ''

Another alternative is to use the soft_int function that Serge Rielau posted
here a while ago. This function would return the number as INT (or NULL if
the string doesn't contain a valid number).

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Troels Arvin - 20 Dec 2007 19:12 GMT

> Another alternative is to use the soft_int function that Serge Rielau
> posted here a while ago. This function would return the number as INT
> (or NULL if the string doesn't contain a valid number).

Make that "softint".

However, in this case, I wouldn't be surprised if the string analysis
method proposed by Tonkuma is actually faster than the "try casting
through a function which calls a procedure" method which is used by
Serge's softint solution.

Signature

Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

Reply to this Message

Knut Stolze - 21 Dec 2007 17:12 GMT

>> Another alternative is to use the soft_int function that Serge Rielau
>> posted here a while ago. This function would return the number as INT
[quoted text clipped - 6 lines]
> through a function which calls a procedure" method which is used by
> Serge's softint solution.

No question about that. It really depends on what the OP wants to do. If
it is something like:

CASE
WHEN isDigit(str)
THEN INT(str)
END

Then Serge's function may be a perfect match. If it is really only a test,
then I'd go with Tonkuma's approach, of course.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

--CELKO-- - 28 Dec 2007 23:06 GMT

>> How can I check if a char/string is a digit/number in an SQL UDF? <<

Wouldn't it be better to add a " CHECK( foobar IS SIMILAR TO
' [:DIGITS:]+') to the column and prevent the bad data?

Reply to this Message

Tonkuma - 29 Dec 2007 01:25 GMT

Although, DB2 doesn't support "SIMILAR TO" syntax,
Knut showed very general solution for regex(s) UDF(s) in
"Bringing the Power of Regular Expression Matching to SQL"
http://www.ibm.com/developerworks/db2/library/techarticle/0301stolze/0301stolze.html

Another more poor and simple way may be ....
CREATE TABLE T_check_isdigit
(key INTEGER NOT NULL PRIMARY KEY
,char_str VARCHAR(10) NOT NULL
CHECK( TRANSLATE(char_str,'*',' 0123456789') = '' )
);

Reply to this Message

--CELKO-- - 29 Dec 2007 13:48 GMT

>> Although, DB2 doesn't support "SIMILAR TO" syntax, .. <<

Sorry -- I write ANSI/USO Standard SQL automatically.

Reply to this Message