number system conversion 2

[CIMTEET]

I found RawtoHex and HextoRaw functions, but are there any other functions to do other number system conversion?

 

[LKBrwnDBA]

NO.

You would need to write your own function(s).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

CIMTEET,

I have a function that will convert any base (btw 2-36) to any base (btw 2-36). If you want it, let me know, and I'll post it here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

Heck ya!! Thanks.

 

[SantaMufasa]

Here ya' go:

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

Let me know your reactions to this function.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

Ok, I just found out that the number being converted is hex to this weird type of "number" I have never seen before. The numbers go from 0 through 7 then starting with A moving to Z. There is a binary representation:

00000 = 0
00111 = 7
01000 = A
11111 = Z

Does your code handle this type of thing?

 

[SantaMufasa]

...are there any other functions to do other number system conversion?

Unfortunately, we are working from very sparse specifications. We don't know specifically either your objective or the input/output nature of your data.


Can you explain under what circumstances "01000 = A" and "11111 = Z" and how your code scheme represents the other alphabetic values in between A and Z? I'm sure we can come up with an appropriate function to do what you want, but we must first understand the coding algorithms.

(Is this a classroom assignment?)



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

We are bring in large amounts of data. Basically this number convention saves space in data transmission. I had heard that this is an international standard so my hope was that something had been done. But I could write the code myself if I had too.

No, unfortunately this is not a school assignment but a job assignment. I am not to new to coding, just a new born to the pl/sql and oracle world. You have helped me multiple times and I appreciate it, even if you have to draw the line here.

Greg

 

[CIMTEET]

01234567ABCDEFGHJKLMNPQRSTUVWXYZ

Notice that the "O" and the "I" are gone. They get mixed up with the "0" and "1". (thats how it was explained to me)

 

[CIMTEET]

An example of a Track Number would be EC001. The EC would be tranlated 11000 | 1010 and then the 001 would be in octal: 000 | 000 | 001. I cannot explain why this is currently for different reasons but this number actually comes from the decimal number 201729. Which trying to explain this to you is showing possible missing information. I will explore this before I post again.

 

[CIMTEET]

Sorry, the C was 01010. The first two characters are 5 digit, then I heard the 3 digits were for octal numbers.

 

[CIMTEET]

ok, don't rack your brain on this tonight, I just ran numbers to try to convert the EC001 to binary numbers to decimal and came up with garbage, so I have more questions to ask tomorrow. Thanks

 

[karluk]

That is one ugly numbering system you have there, CIMTEET. It is basically a hybrid of base 8 and base 32. Starting with "EC001", you would convert "EC" to decimal using a base-32-to-base-10 algorithm, getting 394. Then multiply 394 by 512 to adjust for the three octal digits on the right. So 394*512 = 201728. Then convert "001" to decimal using a base-8-to-base-10 algorithm and add it to the result. The result is 201728+1 = 201729, which is what you were expecting.

On the other hand, why anyone would choose to store numbers this way is something of a mystery. It seems to me that simply using pure base 32, instead of this weird hybrid, would work better. You would get the advantage of better data compression and also be able to use SantaMufasa's script for base conversions.

 

[CIMTEET]

How did you know that??????

 

[CIMTEET]

How did you do the base32 to base10?

 

[SantaMufasa]

Is this a classroom assignment?

I wasn't casting any aspirsions, CIMTEET. It just seemed like a very obscure exercise without your disclosing the background that you have since given us.

Basically this number convention saves space in data transmission.

If you weigh the transmission/storage costs of sending "201729" versus "EC001", against the avoidable costs for both 1) CPU cycles required to code/decode the HexOctal hybrid (forever), and 2) the time value that just the three of us have invested in simply understanding (then dealing with) this issue, then I'll guarantee that using this HexOctal coding has been a very bad investment.


Whoever chose this scheme based upon saving "space in data transmission" should be the poster boy for "penny wise...£ foolish". From the Oracle perspective, alone, there is an immediate net loss by using HexOctal (represented as character). Here is proof that "EC001" consumes 25% more space than its decoded equivalent "201729":

SQL> select dump(201729) "201729", dump('EC001') EC001 from dual;

201729                    EC001
------------------------- ----------------------------
Typ=2 [b]Len=4[/b]: 195,21,18,30 Typ=96 [b]Len=5[/b]: 69,67,48,48,49

Notice that storage consumption of "201729" is only 4 bytes ("Len=4"), while the consumption of "EC001" is 5 bytes ("Len=5").

So, on all fronts, this scheme is wasting resources...saving nothing. Let us know your (and your colleagues') thoughts on this topic.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

Today for the first time I saw the 201729 number actually in the system looking at some post test work from an analyst. I do know that all data coming into the data warehouse is so time sensitive that it is just stored as binary in one half of the schema during certain functions of software operation. I do not know the operational relationship between these two numbers enough but I need to ask again about the origin of the numbers. We have our system number which and then an outside representation of hte same number from another source 'EC001' The answers to my questions were vague but I know who to ask.