All Forums Database
dsreddy117 20 posts Joined 11/07
10 Dec 2007
how to get only integer values from character field

Hi, I have a table with one of the columns defined as character. This column contains integer values and character values also. I need to copy this data into another table which is having the integer column.I need to copy all the values having integer values. If the field contains character values then I need to put NULL value. For example if table1 contains value like 123 456 54A A-5 789Then my target table should have the following records 123 456 NULL NULL 789Thanks in advance.

Balamurugan B 81 posts Joined 09/07
11 Dec 2007

Hi Rahul,If you have a fixed column length of 3 as per your example, you can try with the following code:insert into....(select case when substr(c1,1,1) in ('1','2',...'0') and substr(c1,2,1) in ('1','2',...'0') and substr(c1,3,1) in ('1','2',...'0') Then c1 Else NULL /* default Else value is also null*/Endfrom table1);Regards,Balamurugan

Regards,
Balamurugan

11 Dec 2007

A better way would be to use the user defined function.You do not have to worry about the field size.you can find this function "is_integer"at the following linkhttp://www.teradataforum.com/is_integer.htmload this function and it is very helpful.

dsreddy117 20 posts Joined 11/07
12 Dec 2007

Balamurugan & Feroz, Thanks for your the solutions. Both the answers are useful to me.

You must sign in to leave a comment.