Hi Teradata gurs,
Could you please any body help me how to exceute only integer values in charcater
Suppose i have data like below :
I want to exceute only integer value exclude character value :
Please help me how to write the query
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.
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.
To extract only integer values u can use the query as below
OTRANSLATE(<columnname>, OTRANSLATE(<columnname>, '.0123456789', ''), '')