SQL Query to find a field is numeric or not

raajan_p · New User Joined: 19 Sep 2005 Posts: 17

Hi,

can any one let me know the query that must be used to find out whether a particular field in a table is either numeric or not.

for eg:

There is field called GROUP_NO.
I want to check whether this field is numeric or not with the help of a query.
It has been told that this field is of 10 bytes length. Is it possible to check whether the field is of 10 bytes length with the help of a query. please let me know if any one has the answers for both the questions.

Thanks
Raajan. P

priyesh.agrawal · Posted: Mon Oct 31, 2005 2:36 pm

In a short you want to see sturcture of the table.

Make a search in the DB2 Section of the forum to see this topic discussed.

Regards,

Priyesh.

dewrid · New User Joined: 28 Oct 2005 Posts: 4

Hi, I will have to check whether there is any function that can help us find out a column's datatype directly, but what I do know is that the table SYSIBM.SYSCOLUMNS contains information about each column and its dataype of all tables in your database. You can query this table with the table name and get the required information, assuming you have the required privilege. Pls let me know your finidings.

avelayudhan · New User Joined: 30 Nov 2005 Posts: 26

I have the same situation where I need to know all records where a column has non-numeric values.

The column is defined as CHAR(5). But this column can contain many possibel values as

1. ' 23'
2. '12345'
3. 'ABCD '
4. 'ABCDE'
5. '09340' and so on.

How can we get records 1,3 and 4 from above records using a query?

ashimer · Posted: Tue Oct 10, 2006 4:14 pm

try out this query ....

select * from table where var like '%0%1%2%3%4%5%6%7%8%9%' ;

where var is the col name .....

this shud help .....

ravven16 · New User Joined: 22 Aug 2006 Posts: 16

Hi,

you can try this query

SELECT * FROM table WHERE COL1
NOT BETWEEN 'AAAAA' AND 'ZZZZZ'

This question will eliminate all values between 'AAAAA' AND 'ZZZZZ' . as per your example it will print only the numeric value.