Cast CHAR/VARCHAR to VARBYTE

Database
Enthusiast

Cast CHAR/VARCHAR to VARBYTE

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:

SELECT *
FROM TABLE
WHERE BITOR('STRING','MASK'XBV) = 'SOMETHING'XBV;

P.S. I've just got lost into Teradata data types and casting !?

Tags (4)
14 REPLIES
Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

Anyone?

Please don't tell me that Teradata is missing this functionality, even MySQL has a cast from string to binary:

CAST(str AS BINARY)
Junior Contributor

Re: Cast CHAR/VARCHAR to VARBYTE

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')

Dieter

Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

The data is already stored as string. I want to search it, checking if it contains given ASCII code or not.

Junior Contributor

Re: Cast CHAR/VARCHAR to VARBYTE

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||'%'

Dieter

Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

Thanks Dieter,

but I want to apply a mask to the string, not just one ascii code.

Junior Contributor

Re: Cast CHAR/VARCHAR to VARBYTE

 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?

Dieter

Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

Dieter, those two statements are not mutually exclusive.

Thanks, I'll do it on my own.

Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

Hi Dieter/Veskojl,

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.

Regards,

Suhail

Enthusiast

Re: Cast CHAR/VARCHAR to VARBYTE

could any tell how to convert varbyte data to varchar.

Thanks,

Geeta A. Gwalani