how to extract varbye and Byte colum

[murali_ncm]

Hi Team,

we are using teradata 14.10.

We are trying to extract varbyte and Byte columns. 

for bytes we did the below method 

 SELECT  sample, CAST( LPAD(FROM_BYTES('00'xb||sample, 'BASE16') ,12,'0') AS VARCHAR(12))  as sample2 FROM  DBC.AccessRights   SAMPLE 50 ;

Please assist us how to deal with varbyte. We need to extract it as varchar or blob. 

[DaveWellman]

Hi,

Why do you think that a VARBYTE column needs to be handled differently? The FROM_BYTES function will process both BYTE and VARBYTE columns.

I tried the following (albeit on 15.10 and not 14.10 but I doubt that makes a difference):

CREATE TABLE t1
(col1 INTEGER
,byte_col BYTE(4)
,vbyte_col VARBYTE(4)
);

INSERT INTO t1 VALUES(1,'01020304'xb,'0102'xb);

SELECT col1
     ,CAST( LPAD(FROM_BYTES('00'xb||byte_col, 'BASE16') ,12,'0') AS VARCHAR(12))
    ,CAST( LPAD(FROM_BYTES('00'xb||vbyte_col, 'BASE16') ,12,'0') AS VARCHAR(12))
FROM t1;

The query runs fine, producing the output that I expected.

col1	LPAD(FROM_BYTES(('00'XB||byte_col),'BASE16'),12,'0')	LPAD(FROM_BYTES(('00'XB||vbyte_col),'BASE16'),12,'0')
1	000001020304	                                        000000000102

Does that help?

Cheers,

Dave

[murali_ncm]

Thanks Dave,

But we have varbte size is 64000

CREATE TABLE t1
(col1 INTEGER
,byte_col BYTE(4)
,vbyte_col VARBYTE(64000)
);

this vbyte_col having error logs . (.txt files)

I need to extract this column as BLOB . Please assist.  

[DaveWellman]

Hi,

Sorry, I missed the 'varchar or blob' bit in your original post.

For BLOB you should be able to simply CAST the column: CAST(vbyte_col AS BLOB)

Not sure about making this a VARCHAR. The FROM_BYTES documentation says the output is always VARCHAR(64) - which seems a bit strange but I can't check right now.

If you're exporting the data out of Teradata then I'd try just selecting the column and letting the client program deal with it, the column will be exported as a series of bytes and your program can deal with it how it likes.

HTH

Dave