How to exceute only integer records in character column

UDA

How to exceute only integer records in character column

Hi Teradata gurs,

Could you please any body help me how to exceute only integer values in charcater

Suppose i have data like below :

Column A

A123RAJ

RAJ345venkat

Prakash234RAJ

I want to exceute only integer value exclude character value :

Column A

123

345

234

Please help me how to write the query

Regards,

Anil

3 REPLIES
Enthusiast

Re: How to exceute only integer records in character column

Through direct supported SQL I can only think of recursive query. You will need to check from start of the string to length of the the string and if numberic than concatenate to existing data.

If you insert something in a table as a column.you usually expect it to be atomic therefore database would usuually support operations using the column value in its entireity not for manipulating data inside column.

Junior Contributor

Re: How to exceute only integer records in character column

Of course the best approach is a UDF.

If oTranslate is installed this is an old trick to get rid of any unwanted chars:

oTranslate(a, '.' || oTranslate(a, '.0123456789', '.'), '.')

When your version of oTranslate is not considering empty strings as NULL (stupid Oracle) it's easier:

oTranslate(a, oTranslate(a, '0123456789', ''), '')

Without UDFs you could avoid recursion, too:

case when substring(a from 1 for 1) between '0' and '9' then substring(a from 1 for 1) else '' end ||

case when substring(a from 2 for 1) between '0' and '9' then substring(a from 2 for 1) else '' end ||

case when substring(a from 3 for 1) between '0' and '9' then substring(a from 3 for 1) else '' end ||

...

Of course this assumes column a has a short length.

Dieter

Enthusiast

Re: How to exceute only integer records in character column

To extract only integer values u can use the query as below

SELECT

OTRANSLATE(<columnname>, OTRANSLATE(<columnname>, '.0123456789', ''), '')

FROM

<<tablename>>

thanks

Venkat