function bin_to_num with sql-server 1

[stwi1974]

Hi,

is there any equivalent to bin_to_num with sql-server?

thanks in advance

 

[lewisp]

If you mean 'what is the equivalent of sql-server bin_to_num in Oracle', I dont think there is one (unless Sem, Santamufasa et al prove me wrong ;-) ). You can use a function though:

CREATE OR REPLACE FUNCTION bin_to_num (n1 in varchar2)
RETURN NUMBER IS
  n2 NUMBER := 0;
  l  NUMBER := Length(n1);
BEGIN
  IF  Nvl(l,0) != 0
  AND Replace(Replace(n1,'0',NULL),'1',NULL) IS NULL
  THEN
    FOR c IN REVERSE 1..l
    LOOP
      n2 := n2 + To_Number(Substr(n1,c,1)) * Power(2,l-c);
    END LOOP;
--
    Return n2;
  ELSE
    Return 0;
  END IF;
END;

Then use it like this:

SQL> SELECT bin_to_num('101010') from dual;

BIN_TO_NUM('101010')
--------------------
                  42

 

[SantaMufasa]

Lewis and Zerberus,

I, too, am unaware of an Oracle built-in for function base-2-to-base-10 conversions. Instead, I prefer an "any-base-to-any-base" function anyway, so here is my contribution:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: BASECONV.SQL - PL/SQL code to translate positive or 
REM       negative integer values from any base (radix)
REM       (base 36 max.) to its counterpart value in any other
REM       base (base 36 max.).
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM
REM **************************************************************
REM Usage:
REM       BASECONV (<value>,<source-base>,<target-base>)
REM
REM   ...where <value> is some "number" using valid "numerals" in
REM   the <source-base>. For example, in base 2, valid numerals are
REM   "0" and "1"; in base 36, valid numerals are "0" thru "z".
REM   When using bases 11 thru 36, you specify alpha characters for
REM   numerals beyond decimal '9'. Alpha characters may be in either
REM   upper or lower case.
REM
REM   ...where <source-base> is any base (radix) between 2 and 36.
REM   ...where <target-base> is any base (radix) between 2 and 36.
REM   Be sure to specify both source and target bases in base 10.
REM   
REM Sample invocations:
REM
REM   col x heading "Conversion" format a20
REM   select baseconv(255,10,2) x from dual;
REM   Conversion
REM   ----------
REM   11111111
REM
REM   select baseconv('Zebra',36,10) x from dual;
REM   Conversion
REM   ----------
REM   59454982
REM
REM   select baseconv('aaaaaaaaaaaaaaaa',16,2) x from dual;
REM   Conversion
REM   ----------------------------------------------------------------
REM   1010101010101010101010101010101010101010101010101010101010101010
REM **************************************************************
Create or replace function BaseConv
	( ValueIn	in varchar2	-- incoming value to convert
	, RadFrom	in number	-- source base
	, RadOut	in number	-- target base
	)
	return 		varchar2	-- outgoing value in target base
is
	ValIn		varchar2(1000);
	Sign		char;
	LenIn		number;
	Base10Value	number;
	DigitPool	varchar2(50)	:= '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DigitHold	varchar(1);
	HighPower	number;
	CurrValue	number;
	CurrDigit	number;
	ResultingValue	varchar(2000);
	function GetDigit10 (InDigit in varchar2, RadIn in number) return number
	is
		bad_digit	exception;
		pragma exception_init(bad_digit,-6502);
	begin
		if InDigit = '0' then
			return 0;
		end if;
		DigitHold := upper(InDigit);
		for i in 1..RadIn-1 loop
			if DigitHold = substr(DigitPool,i,1) then
				return i;
			end if;
		end loop;
		raise_application_error(-20000,'Illegal digit, "'||InDigit||'" for base "'||RadIn||'"');
	end;
begin
	ValIn	:=	ValueIn;
	if substr(ValIn,1,1) = '-' then
		Sign	:= '-';
		ValIn	:= substr(ValIn,2);
	else
		Sign	:= null;
	end if;
	LenIn := length(nvl(ValIn,'0'));
	Base10Value	:= 0;
	for i in 1..LenIn loop
		Base10Value	:= Base10Value +
			GetDigit10(substr(ValIn,i,1),RadFrom) * power(RadFrom,LenIn-i);
	end loop;
	for i in 1..1000 loop
		if power(RadOut,i) > Base10Value then
			HighPower := i-1;
			exit;
		end if;
	end loop;
	CurrValue	:= Base10Value;
	ResultingValue	:= null;
	for i in 0..HighPower loop
		CurrDigit := floor(Currvalue / power(RadOut,HighPower-i));
		CurrValue := Currvalue - (CurrDigit * power(RadOut,HighPower-i));
		if CurrDigit = 0 then
			ResultingValue := ResultingValue||'0';
		else
			ResultingValue := ResultingValue||substr(DigitPool,CurrDigit,1);
		end if;
	end loop;
	return sign||ResultingValue;
end;
/

Additionally, I'm having a problem, Lewis, using the code you posted. Here are results from the code on my Oracle 9i (9.2.0.4) database instance:

SQL> CREATE OR REPLACE FUNCTION bin_to_num (n1 in varchar2)
  2  RETURN NUMBER IS
  3    n2 NUMBER := 0;
  4    l  NUMBER := Length(n1);
  5  BEGIN
  6    IF  Nvl(l,0) != 0
  7    AND Replace(Replace(n1,'0',NULL),'1',NULL) IS NULL
  8    THEN
  9      FOR c IN REVERSE 1..l
 10      LOOP
 11        n2 := n2 + To_Number(Substr(n1,c,1)) * Power(2,l-c);
 12      END LOOP;
 13  --
 14      Return n2;
 15    ELSE
 16      Return 0;
 17    END IF;
 18  END;
 19  /

Function created.

SQL> SELECT bin_to_num('101010') from dual;
SELECT bin_to_num('101010') from dual
                                 *
ERROR at line 1:
ORA-01428: argument '101010' is out of range


SQL> select bin_to_num('1') from dual;

BIN_TO_NUM('1')
---------------
              1

1 row selected.

SQL> SELECT bin_to_num('10') from dual;
SELECT bin_to_num('10') from dual
                             *
ERROR at line 1:
ORA-01428: argument '10' is out of range

Lewis, do you have any ideas on what's the issue here?

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:08 (06Apr04) UTC (aka "GMT" and "Zulu"), 19:08 (05Apr04) Mountain Time)

 

[lewisp]

Santa, I've raised a TAR with Oracle on this one. The problem seems to be the Power function. If you have a simple function that has simply

[tt]i := Power(3,2);
Return i;[/tt]

it still gives the error.

I'll keep you posted on the result.

 

[lewisp]

I take that back. It doesnt seem to be the power function at all.....

 

[lewisp]

It seems Oracle 9i is supplied with a BIN_TO_NUM function built in, but it only operates on a bit at a time.

If I change the name of my function it works perfectly.

SQL> select b2n('10101010101010000101011') from dual;

B2N('10101010101010000101011')
------------------------------
                       5592107

This is Oracles attempt:

SQL> select bin_to_num(1,1,1) from dual;

BIN_TO_NUM(1,1,1)
-----------------
                7

 

[stwi1974]

Hey, thanks alot for your replies ^^

i'll have to check out if i can use a function though. I'm executing all the queries out of a framework and i don't know if there are some problems with functions yet.