By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Check varchar field numeric using SQL

P: n/a
Hi,

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

-- peter

Aug 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Aug 11 '06 #2

P: n/a

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.

Aug 11 '06 #3

P: n/a

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

Aug 12 '06 #4

P: n/a

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.

Aug 14 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.