Is there a way to convert string data into varbinary data type. I want to use the BYTE/BIT manipulation functions on a string column, but they can recieve only BYTEINT,INT, BIGINT and VARBYTE data types as arguments.
I tried this kind of cast:
SELECT CAST (CHAR2HEXINT('ABCDE') AS BIGINT);
but this has limited functionality, because the maxlenght of the string.
Any idea how to avoid this?
Basically I want achieve that:
WHERE BITOR('STRING','MASK'XBV) = 'SOMETHING'XBV;
P.S. I've just got lost into Teradata data types and casting !?
Please don't tell me that Teradata is missing this functionality, even MySQL has a cast from string to binary:
CAST(str AS BINARY)
What kind of data is stored in the string? If it's a hexadecimal string why don't you store it as BYTE?
In TD14 there's TO_BYTES/FROM_BYTES, e.g. select to_bytes('abcde', 'ascii')
Searching for a given ASSII code in a string?
Why don't you use the existing string function?
POSITION('0A'xc in str) or WHERE str LIKE '%'||'0A'xc||'%'
I want to use the BYTE/BIT manipulation functions on a string column
I want to search it, checking if it contains given ASCII code or not.
Could you elaborate what you're actually trying to do.
On which TD release?
I'm looking for something to do the reverse: convert a varbyte back to varchar. I'm really in search of a UDF or some method within teradata to convert varbyte to varchar. So I'm hoping you guys can help me out. I need to store all rows in the error table of fastload into a persistent error table(its ddl matches with that of the target table but all fields are all varchar). I could do the method of exporting the records of error table into the client system and read them back again into a table with all varchar but all of this seems unnecessary(and frankly quite crude) to me. I'd like a method to keep the record within teradata itself and achieve this objective. We're on TD 13.10.
My primary objective is to read the error records from a fastload ACQUISITION Phase error table and load it into a table that matches exactly with the target table except all datatypes of this table are varchar. I want to do this without exporting the error records to a client system and then reading them back again into teradata.
PS: I have been posting the above comment in multiple forum posts(on this website) that are related to discussions on the varbyte datatype. One of the forum posts does talk about a UDF: varbytetovarchar.c but I could not find the installable anywhere on the internet. If you know of any such function, please let me know.