Archives of the TeradataForum

Message Posted: Tue, 01 Nov 2016 @ 22:40:30 GMT

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

I have not tried it, but you could try using the regular expression functions and a case statement to determine if the value to be cast consists of digits.

e.g. use a pattern such as ^[+-]?\d*\.?\d*$ Note that this pattern is not perfect as it will match a character string consisting of just a period (i.e. it will match ".") which won't cast.

I shall leave it to you to improve it.

So the sql would be something like:

     Case
         When regexp_instr(value_to_cast, '^[+-]?\d*\.?\d*$') > 0 then cast (value_to_cast as integer)
         Else NULL
     End

Again I have not tested, I shall leave it to you to finish it off.

If you are on a version of Teradata that supports SQL functions, you could define this as an SQL function and reference that in your query - this would make your query a lot cleaner.

Hope this helps

Glenn Mc

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