Обсуждение: BIN()

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

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
>

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

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

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

> 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

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

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

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

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.

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

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