Archives of the TeradataForum

Message Posted: Fri, 30 Jul 1999 @ 13:29:22 GMT

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

I've played with this problem a couple of times and I have been unable to find anything better than SUBSTR'ing the desired nibbles (which is useful because it permits you to cross byte boundaries) and then using the CASE statement to translate. Although it works well enough, it certainly isn't elegant. One nuisance is that it appears that there is a WHEN limit of around 20 within a CASE. And if it's an issue, don't forget to byte-flip.

Here's an example (where BYTEVAL was defined as BYTE(16383) and values where being pulled from within the string for translation):

     , (case when substr(i.byteval,5,1) < '10'xb
                  then (case substr(i.byteval,5,1)
                             when '00'xb then 000000
                             when '01'xb then 010000
                             when '02'xb then 020000
                             when '03'xb then 030000
                             when '04'xb then 040000
                             when '05'xb then 050000
                             when '06'xb then 060000
                             when '07'xb then 070000
                             when '08'xb then 080000
                             when '09'xb then 090000
                             when '0a'xb then 100000
                             when '0b'xb then 110000
                             when '0c'xb then 120000
                             when '0d'xb then 130000
                             when '0e'xb then 140000
                             when '0f'xb then 150000
                             end
                       )
             when substr(i.byteval,5,1) > '0f'xb
              and substr(i.byteval,5,1) < '18'xb
                  then (case substr(i.byteval,5,1)
                             when '10'xb then 160000
                             when '11'xb then 170000
                             when '12'xb then 180000
                             when '13'xb then 190000
                             when '14'xb then 200000
                             when '15'xb then 210000
                             when '16'xb then 220000
                             when '17'xb then 230000
                             when '18'xb then 240000
                             end
                       )
             else null
             end
       )

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