title: How to convert the Long value to String using sql
body: <p>I am doing a long to string conversion using java in following way.</p>
  
  <pre><code>Long longValue = 367L;
  String str = Long.toString(longValue, 36).toUpperCase();
  </code></pre>
  
  <p>this is returning me as value A7. how can achieve this in doing oracle sql.</p>
  
  <p>UPDATED:</p>
  
  <p>Hi, I have analyzed how java code is working then wanted to implement the same thing in procedure. </p>
  
  <p>First point is Input vaues. LONG and Radix. in my case Radix is 36. so i will have values from 1..9A...Z0 It picks up the values from this set only.
  Second point Long value as input. we have to divide this value with radix. if the quotient is more than 36 again we need to divide.</p>
  
  <p>For eaxmple 367 then my converted value is 10(quotient) 7(remainder) that is A7.
              3672 converted value is 102 0 i need to do again for 102 that is 2 -6 so my final value will be 2-6 0 that is 2U0(- means reverse the order).</p>
  
  <p>UPDATE 2:</p>
  
  <p>Using oracle built in functions we can do this. this was solved by my friend and gave me a function.I want to thank my friend. this will give me an out put as follows.</p>
  
  <p>367 then my converted value is 10(quotient) 7(remainder) that is *A*7.(I modified this to my requirement).</p>
  
  <pre><code>FUNCTION ENCODE_STRING(BASE_STRING    IN    VARCHAR2,
                              FROM_BASE      IN    NUMBER,
                              TO_BASE        IN    NUMBER) 
    RETURN VARCHAR2
    IS
        V_ENCODED_STRING    VARCHAR(100);
    BEGIN
  
        WITH N1 AS (
                     SELECT SUM((CASE 
                                     WHEN C BETWEEN '0' AND '9' 
                                         THEN TO_NUMBER(C) 
                                     ELSE 
                                         ASCII(C) - ASCII('A') + 10 
                                 END) * POWER(FROM_BASE, LEN - RN)    
                                ) AS THE_NUM
                     FROM (SELECT SUBSTR(BASE_STRING, ROWNUM, 1) C, LENGTH(BASE_STRING) LEN, ROWNUM RN 
                           FROM DUAL 
                           CONNECT BY ROWNUM &lt;= LENGTH(BASE_STRING))
                    ),
             N2 AS ( 
                     SELECT (CASE 
                                 WHEN N &lt; 10 
                                      THEN TO_CHAR(N) 
                                 ELSE CHR(ASCII('A') + N - 10) 
                             END) AS DIGI, RN
                     FROM (SELECT MOD(TRUNC(THE_NUM/POWER(TO_BASE, ROWNUM - 1)), TO_BASE) N, ROWNUM RN
                           FROM N1
                           CONNECT BY ROWNUM &lt;= TRUNC(LOG(TO_BASE, THE_NUM)) + 1)
                    )
        SELECT SYS_CONNECT_BY_PATH(DIGI, '*') INTO V_ENCODED_STRING  
        FROM N2
        WHERE RN = 1    
        START WITH RN = (SELECT MAX(RN) FROM N2)
        CONNECT BY RN = PRIOR RN - 1;
  
        RETURN V_ENCODED_STRING;
  </code></pre>