convert integer value to binary

Database

convert integer value to binary

Hi 

Please advise in Teradata how to convert integer value to binary and then back to integer ? If there is any function for that ?

For example: integer value is 50, binary will be 00110010 and again from 00110010 back to 50.

Thanking You

Santanu Ghosh

7 REPLIES

Re: convert integer value to binary

Hi Experts

Any update for the question. Also I tried the below SQL. But when I am using column instead of hardcoded value it is giving error.

SEL TO_BYTES ('23', 'base10') ; --> it is running fine

SELECT CAST(23 AS VARCHAR(10)) VL, TO_BYTES(VL,'base10') ; --> it is giving error

Please suggest me on this.

Thanking You

Santanu

Re: convert integer value to binary

Never Mind, I have figured it out. I suppose the below SQL should suffice. If you guys have any better suggestion then please advise.

SEL CAST(23 AS VARCHAR(20) CHARACTER SET UNICODE )  VL,  FROM_BYTES(TO_BYTES( VL, 'BASE10'), 'BASE2') ;

Thanks

San

N/A

Re: convert integer value to binary

Instead of casting the VarChar to Unicode you can use this:

TO_BYTES(VL, TRANSLATE('base10' USING unicode_to_latin))

This UDF needs both parameters of the same character set...

For BYTE-/SMALL-/BIG-/INT you can simply use TO_BYTE(50) to return BYTEs

FROM_BYTES(TO_BYTE(23),'base2') returns a 0/1-string.

But do you need a 0/1-string or just BYTEs?

What are you actually trying to do?

Re: convert integer value to binary

Thanks Dieter for your reply. 

Actually there is a weird requirement which I need to implement.

Say there is one column COL1 which has integer value 23. 

Now first I need to convert that COL1 to 8 bits binary digits (0/1 strings) which is 00010111 (for 23).

Then using different CASE_WHEN statements I need to take substrings of either first 5 bits or last 3 bits from that.

Then I need to append 0 to remaining bits. So in case of last 3 bits it will be 00000111.

Then I have to again change that binary value 00000111 to corresponding integer which will be 7.

I was thinking of using TO_BYTES, FROM_BYTES, CAST, LPAD, SUBSTR all these functions together.

If you can suggest any better way to achieve that it will be really helpful.

Thanks

San

N/A

Re: convert integer value to binary

You don't to convert to a bit-string, there are a lot of built in functions doing bit-manipulation on integers/bytes:

http://www.info.teradata.com/htmlpubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1145_112A/Bit_...

e.g. BITAND(23, '07'xb) returns what you want.

Teradata Employee

Re: convert integer value to binary

Integer math also works in simple cases, and is faster.  

23 MOD 8 = 7

You didn't specify whether you are adding the zeros on the left or right for the other case, but 23 / 8 = 2 or 8*(23/8) = 16

Re: convert integer value to binary

Thank you Dnoeth and Fred for your assistance. My requirement was finally fulfilled by using bit functions and data type functions. If any further help is required I may get in touch with you.

Thanks

San