logo

PL/SQL Fun with Numbers

In this blog we look at the PL/SQL package util_numeric.


Click on the following link view the PL/SQL source code.

UTIL_NUMERIC Package
PL/SQL Package: UTIL_NUMERIC

The package contains the following functions:


UTIL_NUMERIC Functions
Function Name Arguments Description
dectobase p_number: Decimal integer to be converted

,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal		 Returns a string containing the base value of the specified number, e.g. '11' for 3 in base 2
basetodec p_number: A string containing the base value to be converted to decimal. e.g. '10' is the binary string representing 2 in base 10.

,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal		 Returns a number representing the decimal value of the specified base number, e.g. 3 for '11' in base 2.
dectohex p_number: Decimal integer to be converted to Hexadecimal. Returns a string containing the hexadecimal value.
hextodec p_number: Hexadecimal string to be converted to decimal. Returns the decimal value for the hexadecimal specified.
factorial p_number: Positive integer. Returns the factorial of the given number.
factorialr p_number: Positive integer. Returns the factorial of the given number, using recursion.
sort_numbers p_string: A string of numbers, separated by commas, to be sorted

,p_order: Sort sequence 'A' for ascending, all other values Descending.		 Returns a string containing the sorted list of numbers.
num_to_alphanumeric p_number: A positive integer to be converted. Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.
dectoalpha p_number: A positive integer to be converted.

,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.		 Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.
alphatodec p_code: A string containg the alphanumeric code, e.g. 'AA' for 27.

,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.		 Returns the decimal integer value of the specified alphanumeric code, e.g. 28 for 'AB'.


num_to_alphanumeric

The function num_to_alphanumeric converts integers to alphanumeric codes. For example: 1=A, 2=B, 26=Z, 27=AA, 28=AB, 52=AZ, 53=BA etc.
Here’s how the function works:

  1. We start with the input number.
  2. In each iteration, we calculate the remainder after dividing by 26 (the number of letters in the alphabet).
  3. We convert the remainder to the corresponding letter (‘A’ for 1, ‘B’ for 2, and so on).
  4. We prepend the letter to the result string.
  5. We update the input number by subtracting the remainder and dividing by 26.
  6. Repeat until the input number becomes zero.


Now you can use this function to convert numbers to the desired alphanumeric code. For example:
NUM_TO_ALPHANUMERIC(1) returns 'A'.
NUM_TO_ALPHANUMERIC(27) returns 'AA'.
NUM_TO_ALPHANUMERIC(52) returns 'AZ'.
NUM_TO_ALPHANUMERIC(53) returns 'BA'.




In the next blog post, we will look at some PL/SQL date functions.

Previous Blog Next Blog