CODEWITHSUNDEEP
sqloracle-databaseplsql
Superdooperhero
Superdooperhero

Reputation: 8096

Oracle - How to decode a string converted to a number

I have the following Oracle PLSQL function which converts an ascii string to a number:

function str2num
(
   p_string in varchar2
)  return number is

l_i      number(15);
l_c      number(15);
l_answer number(38);

begin

   l_answer := 0;
   for l_i in 1..length(p_string) loop
      l_c := ascii(substr(p_string, l_i, 1));
      l_answer := l_answer + (l_c * power(2, (l_i - 1) * 8));
   end loop;

   return l_answer;

end str2num;

I would like to write a function to convert that number back to the string.

Basically the function converts a character to an ascii code and then shifts the value left in binary by multiplying by 2^(char_pos-1)*8.

So the string 'Ace' would be encoded as follows:

Chr Ascii   Pos Pos - 1 Value   Binary Value
A   65  1   0   65      00000000000000001000001
c   99  2   1   25344   00000000110001100000000
e   101 3   2   6619136 11001010000000000000000
            final sum =     6644545 11001010110001101000001

How can I do the opposite function?

Upvotes: 1

Views: 1671

Answers (2)

user5683823
user5683823

Reputation:

Perfect "practice" for recursive functions!

You still need to write some error handling; and, since the number data type is limited, it may be better to translate to and from binary (rather than base ten) numbers, and write the binary numbers as strings rather than true numbers, so you have no limit on length. (Using CLOB if need be.) But that is a different problem.

str2num

create or replace function str2num
(
   p_string in varchar2
)  return number is
begin
   return ascii(substr(p_string, 1, 1)) 
          + case when length(p_string) = 1 then 0
                 else 256 * str2num(substr(p_string, 2)) end;
end str2num;
/

select str2num('Ace') from dual;

STR2NUM('ACE')
--------------
6644545

num2str 

create or replace function num2str
(
   p_number in number
)  return varchar2 is
begin
   return chr(mod(p_number, 256))
          || case when p_number >= 256 then num2str(trunc(p_number/256)) end;
end num2str;
/

select num2str(6644545) from dual;

NUM2STR(6644545)
----------------
Ace

Upvotes: 0

MT0
MT0

Reputation: 167972

FUNCTION str2num( p_str IN VARCHAR2 ) RETURN NUMBER
IS
  p_num NUMBER(38,0) := 0;
BEGIN
  FOR i IN REVERSE 1 .. LEAST( LENGTH( p_str ), 15 )
  LOOP                                       -- A NUMBER(38,0) can only fit 15 characters.
    p_num := p_num + ASCII( SUBSTR( p_str, i, 1 ) ) * POWER( 256, i - 1 );
  END LOOP;
  RETURN p_num;
END str2num;

FUNCTION num2str( p_num IN NUMBER ) RETURN VARCHAR2
IS
  p_str VARCHAR2(15);                        -- A NUMBER(38,0) can only fit 15 characters.
  p_var NUMBER(38,0) := p_num;
BEGIN
  WHILE p_var > 0 LOOP
    p_str := p_str || CHR( MOD( p_var, 256 ) );
    p_var := FLOOR( p_var / 256 );
  END LOOP;
  RETURN p_str;
END num2str;

Upvotes: 2

Related Questions