Archives of the TeradataForum

Message Posted: Tue, 11 Jan 2011 @ 09:11:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello experts,

We have a situation in which we need to convert a string to a number. We were using a method to check to determine whether the incoming value is number or not and then casting the value as a number.

The method we used is

     CASE
         WHEN (    ( POSITION(' ' IN TRIM(STR_NUM)) > 0 )
                OR (UPPER(TRIM(STR_NUM)) (CASESPECIFIC) <>
     LOWER(TRIM(STR_NUM)) (CASESPECIFIC))
              )
              THEN 0
         ELSE CAST(STR_NUM AS REAL)
     END as STR_NBR

We end up in another issue in which there can be special character in the incoming value and this conversion will fail if there is a special character.

We can really go back to source system and ask them to correct now.

Could you please help me to resolve this problem?

Is there is any function defined in Teradata which will help me achieving this.

Thanks,

Prasad K K

	<Prev	Next>		<<First	<Prev	Next>	Last>>