Archives of the TeradataForum

Message Posted: Fri, 14 May 2004 @ 20:07:06 GMT

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

I have a peculiar requirement. I have a single byte binary value stored in the middle of a string. I need to retrieve that value and store the number in a table using SQL.

Most of the time, the value is not considered a numeric value and therefore fails during conversion to the target SMALLINT column.

I've tried multiple levels of CASTing with no success. It seems that the only way to do this job is with a CASE construct, with 256 possibilities. Like this:

     CASE SUBSTR(MSG_DATA,45,1)
     WHEN X'00' THEN 0
     WHEN X'01' THEN 1
     WHEN X'02' THEN 2
     ...etc...
     WHEN X'FF' THEN 255
     END (SMALLINT)

Any other ideas?

Thanks to all. TK

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