isdigit ?

[E.Bartosiewicz]

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

[Tonkuma]

On Dec 20, 10:17 pm, [e-mail] wrote:

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') = ''

[Knut Stolze]

Tonkuma wrote:

On Dec 20, 10:17 pm, [e-mail] wrote:

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

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Troels Arvin]

On Thu, 20 Dec 2007 18:27:17 +0100, Knut Stolze wrote:

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.

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

[Knut Stolze]

Troels Arvin wrote:

On Thu, 20 Dec 2007 18:27:17 +0100, Knut Stolze wrote:

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

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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[--CELKO--]

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

[Tonkuma]

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/db...301stolze.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') = '' )
);

[--CELKO--]

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

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