Archives of the TeradataForum

Message Posted: Wed, 07 Dec 2005 @ 13:25:05 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Check Values is Numeric?

From:		Coffing Christopher

You Wrote:

<-- Anonymously Posted: Wednesday, December 07, 2005 01:47 -->

Is there any function to check a value is Numric?. suppose I have a tb1 say table1 and a column col1 as char(30). After inserting values('AAA','AAB',AAC','111') to col1 in the table tb1. How to check the values in col1 is numeric?. In this example value '111' is numeric and other values non numeric.

First question: Did you mean to set the col1 as char(30) or should the col1 be char(3)? Based on the values that you inserted into col1 it appears that char(3) was the intended length. If so then you have a finite amount of choices to cross reference to determine numeric value. (ie: '000','001','011','111'...'999')

You could cross reference these values with an in statement or a cross reference table.

Another option is based on whether the values in col1 will always be either alpha ('AAA') or Numeric('111') and never AlphaNumeric ('A11') then you could use the following statement

     Select col1
       from table1
       where col1 like any ('%0%', '%1%', ... '%9%')

This would select only columns with a numeric character in the value set.

I am sure there are others with more creative ideas than this but hopefully it gets you thinking in the right direction.

Chris Coffing


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference