CODEWITHSUNDEEP
sqloracle-database
arsm4
arsm4

Reputation: 101

Oracle version number to integer

I am using the following query to get the version:

select version FROM V$INSTANCE;

What I want here is that the output be in integer format, so for 11.0.2.4, it should be 11024. I tried to_number as well as regex_substr but am unable to figure out how I can do this using PL/SQL.

Kindly provide some guidance here.

Upvotes: 0

Views: 713

Answers (2)

Nick
Nick

Reputation: 147166

To avoid collisions between version numbers, you need to

  1. split the version number into its components;
  2. convert each component to a 2-digit number (with leading 0 as required); and
  3. join them together again

This query works on a table (v) of sample version numbers (11.0.2.4, 11.1.11.2, 11.11.1.2):

WITH cte AS (
  SELECT DISTINCT "version", level AS l, REGEXP_SUBSTR("version", '\d+', 1, level) AS num 
  FROM v
  CONNECT BY REGEXP_SUBSTR("version", '\d+', 1, level) IS NOT NULL
)
SELECT "version", LISTAGG(TRIM(TO_CHAR(num, '09'))) WITHIN GROUP (ORDER BY l) AS "version number"
FROM cte
GROUP BY "version"

Output (for the sample data)

version     version number
11.0.2.4    11000204
11.1.11.2   11011102
11.11.1.2   11110102

Demo on SQLFiddle

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use REPLACE:

select TO_NUMBER(REPLACE(version, '.', '')) FROM V$INSTANCE;

Beware of potential collisions. Examle: 11.1.11.2 and 11.11.1.2 give the same output 111112.

Upvotes: 0

Related Questions