Check varchar field numeric using SQL

[kanpeter]

Hi,

How can I check if all bytes in a varchar field is all numeric using
SQL ?

-- peter

[Knut Stolze]

ka******@graduate.hku.hk wrote:

Hi,

How can I check if all bytes in a varchar field is all numeric using
SQL ?

You could:
(1) use a loop and check each character manually
(note: signs and exponents for floating point numbers)
(2) try to convert the value and intercept exceptions for non-digits
(3) use regular expression matching:
http://www.ibm.com/developerworks/db...301stolze.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

ka******@graduate.hku.hk wrote:

Hi,

How can I check if all bytes in a varchar field is all numeric using
SQL ?

-- peter

WHERE TRANSLATE(<field>, '', '1234567890') = '' AND <field<''

B.

[Tonkuma]

WHERE TRANSLATE(<field>, '*', ' 1234567890') = ''
If there is a possibility of "length(<field>) = 0", you had better to
use Brian's way.

[Brian Tkatch]

Tonkuma wrote:

WHERE TRANSLATE(<field>, '*', ' 1234567890') = ''
If there is a possibility of "length(<field>) = 0", you had better to
use Brian's way.

Thanx Tonkuma.

When i replied before i was thinking "How would Tonkuma do it?".

In my mind, TRANSLATE is your function. :)

B.