logo

PL/SQL Fun with Numbers

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


Click on the following links to view the PL/SQL source code.

GitHub Repo
util_numeric package spec

util_numeric package body

View the package specification for instructions on how to call the following functions:


UTIL_NUMERIC Functions
Function Name Description
binary_search_leftmost Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns leftmost position of duplicate values.
binary_search_rightmost Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns rightmost position of duplicate values.
binary_search_nearest Returns position of value in array nearest to the target value.
binary_search_predecessor Returns position of nearest lower value.
binary_search_successor Returns position of nearest higher value.
binary_search_range Returns count of numbers in range.
binary_search_rank Returns count of elements preceding target.
dectobase Returns a string containing the base value of the specified number, e.g. '11' for 3 in base 2
basetodec Returns a number representing the decimal value of the specified base number, e.g. 3 for '11' in base 2.
dectohex Returns a string containing the hexadecimal value.
hextodec Returns the decimal value for the hexadecimal specified.
factorial Returns the factorial of the given number.
factorialr Returns the factorial of the given number, using recursion.
sort_numbers Returns a string containing the sorted list of numbers.
num_to_alphanumeric Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'. This is how columns are labelled in Excel.
dectoalpha Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.
alphatodec Returns the decimal integer value of the specified alphanumeric code, e.g. 28 for 'AB'.
remove_duplicates_list Returns a string of comma separated numbers without duplicate values.


num_to_alphanumeric

The function num_to_alphanumeric converts integers to alphanumeric codes, similarly to how Excel labels columns. 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