Postgres Pro
Downloads
Home   >   mailing lists

Thread: BIN()

BIN()

From
Christopher Kings-Lynne
Date:
Hi guys,

How would I go about implementing MySQL's BIN() function easily in PL/SQL.

mysql> SELECT BIN(12);        -> '1100'

Basically it converts a bigint to a string containing 1's and 0's.

I've tried messing about with bit() types, but those types lack casts to 
text, etc.  And they are left padded with many zeros.

Any ideas?

Chris

Re: BIN()

From
Andrew Dunstan
Date:
here's a plperl version :-)  :

create or replace function bin(bigint) returns text language plperl as $$
 my $arg = $_[0] + 0; my $res = ""; while($arg) {   $res = ($arg % 2) . $res;   $arg >>= 1; } return $res;

$$;

cheers

andrew



Christopher Kings-Lynne wrote:

> Hi guys,
>
> How would I go about implementing MySQL's BIN() function easily in 
> PL/SQL.
>
> mysql> SELECT BIN(12);
>         -> '1100'
>
> Basically it converts a bigint to a string containing 1's and 0's.
>
> I've tried messing about with bit() types, but those types lack casts 
> to text, etc.  And they are left padded with many zeros.
>
> Any ideas?
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Re: BIN()

From
Michael Fuhr
Date:
On Tue, Nov 29, 2005 at 09:46:13PM -0500, Andrew Dunstan wrote:
> create or replace function bin(bigint) returns text language plperl as $$
> 
>  my $arg = $_[0] + 0;
>  my $res = "";
>  while($arg)
>  {
>    $res = ($arg % 2) . $res;
>    $arg >>= 1;
>  }
>  return $res;
> 
> $$;

Any reason not to use sprintf("%b", $_[0])?

-- 
Michael Fuhr

Re: BIN()

From
Michael Fuhr
Date:
On Tue, Nov 29, 2005 at 07:57:58PM -0700, Michael Fuhr wrote:
> Any reason not to use sprintf("%b", $_[0])?

Or something like this in SQL or PL/pgSQL:

test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0');ltrim 
-------1100
(1 row)

-- 
Michael Fuhr

Re: BIN()

From
Christopher Kings-Lynne
Date:
>>create or replace function bin(bigint) returns text language plperl as $$
>>
>> my $arg = $_[0] + 0;
>> my $res = "";
>> while($arg)
>> {
>>   $res = ($arg % 2) . $res;
>>   $arg >>= 1;
>> }
>> return $res;
>>
>>$$;
> 
> 
> Any reason not to use sprintf("%b", $_[0])?
> 

All very well and good, but it has to be PL/SQL preferably or PL/pgSQL.  I can write it in PL/PGSQL easily enough I
guessbut I was hoping 
 
there was a neato shortcut.

Chris

Re: BIN()

From
Christopher Kings-Lynne
Date:
> Or something like this in SQL or PL/pgSQL:
> 
> test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0');
>  ltrim 
> -------
>  1100
> (1 row)

Sweeeeet.  Good old i/o functions.

Chris

Re: BIN()

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0');
>> ltrim 
>> -------
>> 1100
>> (1 row)

> Sweeeeet.  Good old i/o functions.

Who needs the I/O functions?  Just cast int to bit(n).
        regards, tom lane

Re: BIN()

From
Christopher Kings-Lynne
Date:

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 
>>>test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0');
>>>ltrim 
>>>-------
>>>1100
>>>(1 row)
> 
> 
>>Sweeeeet.  Good old i/o functions.
> 
> 
> Who needs the I/O functions?  Just cast int to bit(n).

Then how do you remove all leading zeros, and make sure you choose a 
high enough 'n'?

Chris

Re: BIN()

From
Andrew Dunstan
Date:

Michael Fuhr wrote:

>On Tue, Nov 29, 2005 at 09:46:13PM -0500, Andrew Dunstan wrote:
>  
>
>>create or replace function bin(bigint) returns text language plperl as $$
>>
>> my $arg = $_[0] + 0;
>> my $res = "";
>> while($arg)
>> {
>>   $res = ($arg % 2) . $res;
>>   $arg >>= 1;
>> }
>> return $res;
>>
>>$$;
>>    
>>
>
>Any reason not to use sprintf("%b", $_[0])?
>
>  
>

TIMTOWTDI, as we have seen

(also I had forgotten %b if I ever knew it)

cheers

andrew

Re: BIN()

From
Tino Wildenhain
Date:
Am Mittwoch, den 30.11.2005, 10:15 +0800 schrieb Christopher
Kings-Lynne:
> Hi guys,
> 
> How would I go about implementing MySQL's BIN() function easily in PL/SQL.
> 
> mysql> SELECT BIN(12);
>          -> '1100'
> 
> Basically it converts a bigint to a string containing 1's and 0's.
> 
> I've tried messing about with bit() types, but those types lack casts to 
> text, etc.  And they are left padded with many zeros.

In python, I usually go like this:

def trans(value,base="01"):   value,r=divmod(value,len(base))   if value: return trans(value,base)+base[r]   return
base[r]

While base above has a default of "01" which
let it render binary:

trans(10)
-> '1010'

you can use any base you want:

trans(10,"0123456789abcdef")
-> 'a'

and so on.

If you want it easy, just put above code
into a pl/python function.

Or rewrite it in C or pl/pgsql or something.

Re: BIN()

From
Michael Fuhr
Date:
On Wed, Nov 30, 2005 at 07:42:36AM +0100, Tino Wildenhain wrote:
> In python, I usually go like this:

In Ruby (and therefore in PL/Ruby) you could do this:

10.to_s(2)
=> "1010"

10.to_s(16)
=> "a"

-- 
Michael Fuhr

Re: BIN()

From
Tino Wildenhain
Date:
Am Mittwoch, den 30.11.2005, 00:03 -0700 schrieb Michael Fuhr:
> On Wed, Nov 30, 2005 at 07:42:36AM +0100, Tino Wildenhain wrote:
> > In python, I usually go like this:
> 
> In Ruby (and therefore in PL/Ruby) you could do this:
> 
> 10.to_s(2)
> => "1010"
> 
> 10.to_s(16)
> => "a"

is there a 1000.to_s("abcdefghijk") too? :-)
or 212312321.to_s(range(256)) ?