Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (version_number) AS
SELECT  '6.40.123580' FROM DUAL UNION ALL
SELECT '10.10.114580' FROM DUAL UNION ALL
SELECT '10.10.114582' FROM DUAL;
3 rows affected

SELECT version_number,
       TO_NUMBER(SUBSTR(version_number,        1, sep1 -    1)) AS major,
       TO_NUMBER(SUBSTR(version_number, sep1 + 1, sep2 - sep1)) AS minor,
       TO_NUMBER(SUBSTR(version_number, sep2 + 1)) AS patch
FROM   (
  SELECT version_number,
         INSTR(version_number,'.',1,1) AS sep1,
         INSTR(version_number,'.',1,2) AS sep2
  FROM   table_name
)
ORDER BY major DESC, minor DESC, patch DESC
FETCH FIRST ROW ONLY;


VERSION_NUMBER
MAJOR
MINOR
PATCH


10.10.114582
10
10
114582


SELECT version_number,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d+', 1, 1)) AS major,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d+', 1, 2)) AS minor,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d+', 1, 3)) AS patch
FROM   table_name
ORDER BY major DESC, minor DESC, patch DESC
FETCH FIRST ROW ONLY;


VERSION_NUMBER
MAJOR
MINOR
PATCH


10.10.114582
10
10
114582


SELECT MAX(version_number) KEEP (
         DENSE_RANK LAST ORDER BY
         TO_NUMBER(SUBSTR(version_number,        1, sep1 -    1)),
         TO_NUMBER(SUBSTR(version_number, sep1 + 1, sep2 - sep1)),
         TO_NUMBER(SUBSTR(version_number, sep2 + 1))
       ) AS max_version_number
FROM   (
  SELECT version_number,
         INSTR(version_number,'.',1,1) AS sep1,
         INSTR(version_number,'.',1,2) AS sep2
  FROM   table_name
);


MAX_VERSION_NUMBER


10.10.114582