All Forums Database
Santanu84 122 posts Joined 04/13
04 Apr 2016
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

Santanu84 122 posts Joined 04/13
04 Apr 2016

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

Santanu84 122 posts Joined 04/13
04 Apr 2016

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

dnoeth 4628 posts Joined 11/04
05 Apr 2016

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?
 

Dieter

Santanu84 122 posts Joined 04/13
05 Apr 2016

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

dnoeth 4628 posts Joined 11/04
05 Apr 2016

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_Byte.078.001.html
e.g. BITAND(23, '07'xb) returns what you want.

Dieter

Fred 1096 posts Joined 08/04
05 Apr 2016

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

Santanu84 122 posts Joined 04/13
07 Apr 2016

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

You must sign in to leave a comment.