Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Query to find a field is numeric or not

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
raajan_p

New User


Joined: 19 Sep 2005
Posts: 17

PostPosted: Mon Oct 31, 2005 2:03 pm    Post subject: SQL Query to find a field is numeric or not
Reply with quote

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
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Oct 31, 2005 2:36 pm    Post subject: Re: Need a SQL Query
Reply with quote

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.
Back to top
View user's profile Send private message
dewrid

New User


Joined: 28 Oct 2005
Posts: 4

PostPosted: Mon Oct 31, 2005 3:13 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
avelayudhan

New User


Joined: 30 Nov 2005
Posts: 26

PostPosted: Thu Sep 21, 2006 8:53 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Oct 10, 2006 4:14 pm    Post subject: SQL Query to find a field is numeric or not
Reply with quote

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 .....
Back to top
View user's profile Send private message
ravven16

New User


Joined: 22 Aug 2006
Posts: 16

PostPosted: Tue Oct 10, 2006 8:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us