How to convert string to binary in Teradata?

Question

I don't find any functions to convert string to binary in Teradata Database manual-SQL Reference -Functions and Operators. Cast string to byte does not work too.

SELECT C1, C2 FROM table WHERE C1 < (cast( ('QREPI.\k'||'00'XC||'00'XC||'00'XC||'00'XC||'00' XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||' ..') as byte(24)) )); *** Failure 3532 Conversion between BYTE data and other types is illegal. Statement# 1, Info =0

Anybody knows if Teradata provides a way for the conversion?

Any comments are highly appreciated.

Answer 1

According to their blog you should be able to do it with implicit casting (but not explicit). I take that to mean something like:

SELECT C1, C2 
  FROM table 
  WHERE C1 < ('QREPI.\k'||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||
     '00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||'00'XC||' ..');

Have you tried that?

Answer 2

If all you need is a literal, you can get a binary equivalent of your string like this:

SELECT C1, C2 FROM table
WHERE C1 < '51524550492e5c6b000000000000000000000000'xb

Otherwise, for data that is stored in your tables in hex it could be done within Teradata by writing a new UDF. Or you could export it to a file, transform it with a program, and load it back.