Concatenation with Zero is not occurring properly in code?

Question

I was trying to reverse a number in PL/SQL. It's working fine, but when my number contains any 0, the output is unexpected. For example:

1234 output 4321
1000 output 1
1203 ouput 3021
10001 output 1


DECLARE
r number(9);
num number(9):=&p_num;
BEGIN
    WHILE num>=1 LOOP 
        IF mod(num,10)=0 THEN -- extracting last digit of a number .
            r:=r||0; --if end digit is 0 then concat r with 0
        ELSE
            r:=r||mod(num,10);--if mod is not 0 then it would be last digit.
        END IF; 
            num:=trunc(num/10);--Removing last digit from number
    END LOOP;
dbms_output.put_line(r);
END;

Answer 1

I'm not sure what's going wrong in your code Vineet but perhaps this will work.

DECLARE
    newStr    varchar2(9) = "";
    numStr    varchar2(9) := to_char(&p_num);
    i         number;
BEGIN
    i = length(numStr);
    WHILE i>0 LOOP
        newStr := newStr || substr(numStr, i, i + 1);
        i = i - 1;
    END LOOP;
    dbms_output.put_line(r);
END;

Edit: Or as gabe correctly points out, just use the REVERSE function.

Answer 2

Try changing the type of the variable "r" to varchar2. Since it's declared as number, leading zeros are discarded.

Answer 3

'Reversing a number' is fundamentally a string operation, not a numerical one. Numerically, the reverse of 10, 100, 1000, etc are all 1 - the leading zeroes in the result don't count. And the operation is not, therefore, invertible; all numbers with the same leading (significant) digits and with zero or more trailing zeroes produce the same output when reversed.

So, you need to revise your code to generate a character string, not a number.

Answer 4

You can't preserve leading zeros with numbers; you must use strings (varchar2). Try something like this to see:

DECLARE 
    r varchar2(9); 
    num varchars(9):=&p_num; 
BEGIN 
    r := REVERSE(num);
    dbms_output.put_line(r); 
END; 

Answer 5

The problem is you're dealing with a NUMBER value. When you reverse 1000, you get 0001, which when output unformatted is 1.

What you really need is something akin to:

CREATE OR REPLACE FUNCTION rev(p_num NUMBER)
RETURN VARCHAR2 IS
  v_chr VARCHAR2(50);
BEGIN
  v_chr := p_num;
  IF LENGTH(v_chr) > 1 THEN
    RETURN SUBSTR(v_chr, -1, 1)||rev(SUBSTR(v_chr, 1, LENGTH(v_chr)-1));
  END IF;
  RETURN v_chr;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'Bad Input';
END;
/

Function created

SQL> SELECT rev(100000) FROM dual;

REV(100000)
--------------------------------------------------------------------------------
000001

SQL>