how to get only integer values from character field

Database
Enthusiast

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
789

Then my target table should have the following records
123
456
NULL
NULL
789

Thanks in advance.

3 REPLIES
Enthusiast

Re: how to get only integer values from character field

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*/
End
from table1
);

Regards,
Balamurugan

Enthusiast

Re: how to get only integer values from character field

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 link
http://www.teradataforum.com/is_integer.htm
load this function and it is very helpful.

Enthusiast

Re: how to get only integer values from character field

Balamurugan & Feroz,

Thanks for your the solutions. Both the answers are useful to me.