• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value

by admin

Description

This script creates a function to convert Hexadecimal value to decimal.

Example

SQL>  select hextodec('BABA11') from dual;

HEXTODEC('BABA11')
------------------
          12237329

Starting from Oracle 8i, there is a new format model(‘X’) for the to_number function which can be used to accomplish the same task. For the above example, you could issue the following:

SQL> select to_number('BABA11','XXXXXX') from dual; 

TO_NUMBER('BABA11','XXXXXX')
----------------------------
                    12237329

Execution Environment:

SQL, SQL*Plus, iSQL*Plus

Access Privileges:

No special privileges are required to run this script.

Usage:

$ sqlplus [user]/[pw]@[SCRIPTFILE]

The script

Create or replace FUNCTION hextodec (hexnum in char) RETURN number IS  
 x	number;  
 digits number; 
 result	number := 0;                                            
  
 current_digit char(1);                            
 current_digit_dec number;                                            
BEGIN                                                    
 digits := length(hexnum);                                       
 for x in 1..digits loop                                     
   current_digit := upper(SUBSTR(hexnum, x, 1));                          
   if current_digit in ('A','B','C','D','E','F') then           
     current_digit_dec := ascii(current_digit) - ascii('A') + 10;    
   else                  
     current_digit_dec := to_number(current_digit); 
   end if;                                                            
   result := (result * 16) + current_digit_dec;                  
 end loop;                                                      
 return result;                                                
END; 
/

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle SQL Script: To Obtain Session Information
  2. WebLogic Server Domain: How To Disable the HTTP methods other than GET and POST (such as PUT, DELETE, etc.)
  3. How to find current SQL statement being executed by particular session in Oracle
  4. Unable to create spfile for Oracle ASM instance
  5. Oracle Tablespace Transport for a Single Partition
  6. Beginners Guide to RMAN compression for backups
  7. Oracleasm Service Fails to Start After Upgrade to oracleasm-support-2.1.11-1 RPM Package
  8. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above
  9. Oracle Interview Questions – Indexes on Partitioned Table
  10. How to trace asmcmd command on UNIX/Linux

You May Also Like

Primary Sidebar

Recent Posts

  • pw-cat Command Examples in Linux
  • pvs: command not found
  • pulseaudio: command not found
  • pulseaudio Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright