Convert alphanumeric string to numeric - Oracle

Question

I have this alphanumeric string

000193CA020008AA190038AA

I want it to be a unique number, for example

123456791011121314151617.

Is there a way to do it on PLSQL? Not all aphanumerics are the same length, does not contain hex numbers only.

I have tried

create or replace FUNCTION FN_BOM_STEEL_CHAR_TO_NUMBER(
  value IN VARCHAR2
) RETURN NUMBER DETERMINISTIC
IS
  c        CHAR(1);
  up_value VARCHAR2(5000) := LTRIM(UPPER(value),'0');
  total    NUMBER  := 0;
  len      PLS_INTEGER    := COALESCE(LENGTH(up_value),0);
BEGIN
  FOR i IN 1 .. len LOOP
    c := SUBSTR(up_value,i,1);
    total := total * 36
                   + CASE
                     WHEN '0' <= c AND c <= '9'
                     THEN TO_NUMBER(c)
                     ELSE ASCII(c) - 55
                     END;
  END LOOP;
  RETURN total;
END FN_BOM_STEEL_CHAR_TO_NUMBER;

and it worked really well but I have just found there are some duplicates with different alphanumeric strings, for example

1. 010000AA000001AA020019AA020047AA020138AA020139AA060018AA190016AA

2. 010000AA000001AA020019AA020047AA020138AA020139AA060019AA190016AA

You can see there is a 18 and 19 that makes the difference on them but when I select the function I'm getting the same ID for both. There are more cases like these.

Here's the select I'm trying for those examples

SELECT FN_BOM_STEEL_CHAR_TO_NUMBER('010000AA000001AA020019AA020047AA020138AA020139AA060018AA190016AA
') AS ID
FROM   DUAL;

and this is the ID for both: 3095673299759705115226102843045930360610000000000000000000000000000000000000000000000000000000000

I need them to be different. Thanks in advance!

Answer 1

If you just have the characters 0123456789ABCDEF then you can use:

SELECT TO_NUMBER(
         '000193CA020008AA190038AA',
         'XXXXXXXXXXXXXXXXXXXXXXXX'
       ) AS from_hex_value
FROM   DUAL;

Which outputs:

|            FROM_HEX_VALUE |
| ------------------------: |
| 1906840079024071083309226 |

If you have the characters 0-9A-Z and want to treat it as a base 36 string then you could create the function:

CREATE FUNCTION base36_to_number(
  value  IN VARCHAR2,
  -- The value to parse from base 36 to base 10.
  width  IN INT DEFAULT 0,
  -- The width of the substring to consider; 0 is the entire string.
  offset IN INT DEFAULT 0
  -- The number of widths to offset from the least-significant digit.
) RETURN NUMBER DETERMINISTIC
IS
  c         CHAR(1);
  total     NUMBER := 0;
  len       PLS_INTEGER    := COALESCE(LENGTH(value),0);
  start_pos PLS_INTEGER;
  end_pos   PLS_INTEGER;
BEGIN
  IF width < 0 THEN
    RAISE_APPLICATION_ERROR( -20000, 'Invalid width.' );
  ELSIF offset < 0 THEN
    RAISE_APPLICATION_ERROR( -20000, 'Invalid offset.' );
  ELSIF width = 0 THEN
    start_pos := 1;
    end_pos   := len;
  ELSIF width * offset > len THEN
    RETURN 0;
  ELSIF width * (offset + 1) > len THEN
    start_pos := 1;
    end_pos   := len - width  * offset;
  ELSE
    start_pos := len - width * ( offset + 1 ) + 1;
    end_pos   := start_pos + width - 1;
  END IF;

  FOR i IN start_pos .. end_pos LOOP
    c := SUBSTR(value,i,1);
    total := total * 36
                   + CASE
                     WHEN '0' <= c AND c <= '9'
                     THEN TO_NUMBER(c)
                     WHEN 'A' <= c AND c <= 'Z'
                     THEN ASCII(c) - 55
                     WHEN 'a' <= c AND c <= 'z'
                     THEN ASCII(c) - 87
                     ELSE NULL
                     END;
  END LOOP;
  
  total := total * POWER( 36, width * offset );
  RETURN total;
END;
/

Then:

SELECT BASE36_TO_NUMBER('000193CA020008AA190038AA') AS from_base36_value
FROM   DUAL;

Outputs:

|                FROM_BASE36_VALUE |
| -------------------------------: |
| 16743829188442650222733747166386 |

The maximum size of a NUMBER is 38 decimal digits and you cannot fit more than a 24 character base-36 number into this. If you have a larger value then you will need to convert it to multiple numbers:

I.e.:

CREATE TABLE table_name ( value ) AS
SELECT '010000AA000001AA020019AA020047AA020138AA020139AA060018AA190016AA' FROM DUAL UNION ALL
SELECT '010000AA000001AA020019AA020047AA020138AA020139AA060019AA190016AA' FROM DUAL UNION ALL
SELECT 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZzzzzzzzzzzzzzzzzzzzzzzzz' FROM DUAL;

Then:

SELECT BASE36_TO_NUMBER( value, 24, 0 ) AS low_value,
       BASE36_TO_NUMBER( value, 24, 1 ) AS mid_value,
       BASE36_TO_NUMBER( value, 24, 2 ) AS high_value
FROM   table_name;

Outputs:

                             LOW_VALUE |                                                                   MID_VALUE |                                                                                           HIGH_VALUE
-------------------------------------: | --------------------------------------------------------------------------: | ---------------------------------------------------------------------------------------------------:
   34663123071664682325935025837090322 |    777948567801018784511679412336101916888000000000000000000000000000000000 |    3095673299759705115226102065097362559570000000000000000000000000000000000000000000000000000000000
   34663123071664682329591184277153298 |    777948567801018784511679412336101916888000000000000000000000000000000000 |    3095673299759705115226102065097362559570000000000000000000000000000000000000000000000000000000000
22452257707354557240087211123792674815 | 504103876157462118901767181449118688664000000000000000000000000000000000000 | 4011991914547630480065052883598567655228000000000000000000000000000000000000000000000000000000000000

db<>fiddle here