• 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. Background Processes Specific to Oracle RAC
  2. Shell Script to run DataPump Import Export
  3. Oracle Database: How to catalog tape backup pieces
  4. Beginners Guide to Oracle Database 18c Multitenant Architecture
  5. How to Monitor Process Memory Usage on Oracle Pluggable Databases
  6. What are Oracle Key Vault Roles
  7. Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
  8. Cron Script does not Execute as Expected from crontab – Troubleshoot
  9. How to Connect to an Oracle Pluggable Database (PDB)
  10. How to quiesce an Oracle 12c RAC database

You May Also Like

Primary Sidebar

Recent Posts

  • qemu-system-x86_64: command not found
  • timedatectl: command not found
  • mpirun.openmpi: command not found
  • startkde: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright