SQL sort by version "number", a string of varying length

Question

I'm trying to create an SQL query that will order the results by a version number (e.g. 1.1, 4.5.10, etc.)

Here's what I tried:

SELECT * FROM Requirements 
    WHERE Requirements.Release NOT LIKE '%Obsolete%' 
    ORDER BY Requirements.ReqNum

Now, the ReqNum field is a string field and unfortunately I can't change it to a float or something like that because I have requirement numbers like 162.1.11.

When I get the results back, I'll get ordering like this:

1.1
1.10
1.11
1.3

How can I write a query that will sort by lexicographic order?

... or,

How can I correctly sort the data?

Thanks for the input in advance!

Answer 1

For my case: a mixture of three and four part version numbers, some with trailing zeros on SQL Server 2012, none of the solutions using PARSENAME or hierarch_id worked and the string parsing solutions using indexes etc. were either unsuitable or very difficult to parse.

A slight modification to the answer here: https://stackoverflow.com/a/58836025/276874, for a question closed as a duplicate of this one, finally fit the bill, my modified version to handle three or four part versions below:

;WITH SplitXml
AS
(
    SELECT version
        ,CAST('<x>' + REPLACE(version, '.', '</x><x>') + '</x>' AS XML) AS Parts
    FROM tblVersion
),
SplitIntegers AS
(
    SELECT
        version
        ,CAST(Parts.value(N'/x[1]', 'varchar(10)') AS INT) [Major]
        ,CAST(Parts.value(N'/x[2]', 'varchar(10)') AS INT) AS [Minor]
        ,CAST(Parts.value(N'/x[3]', 'varchar(10)') AS INT) AS [Patch] 
        ,ISNULL(CAST(Parts.value(N'/x[4]', 'varchar(10)') AS INT), 0) AS [SR] 
    FROM
        SplitXml
)
SELECT TOP 1
    @version = [version]
FROM
    SplitIntegers
ORDER BY
    [Major] DESC,
    [Minor] DESC,
    [Patch] DESC,
    [SR] DESC;

Answer 2

Here is an ORACLE expression you can use in an ORDER BY:

select listagg(substr('0000000000' || column_value,-9), '.') within group(order by rownum) from xmltable(replace(version, '.',','))

assuming your version column has only dot as separator (any number of levels). (if not, up to you to change the replace by e.g. translate(version, '.-', ',,'))

Answer 3

If the column type for version is varchar the sorting is done as expected. This is beacuse varchar is not padded by spaces.

Answer 4

A slight variation on @vuttipong-l answer (T-SQL)

SELECT VersionNumber
FROM (
SELECT '6.1.3' VersionNumber UNION
SELECT '6.11.3' UNION
SELECT '6.2.3' UNION
SELECT '6.1.12' 
) AS q
ORDER BY cast('/' + VersionNumber + '/' as hierarchyid)

Works in SQL Server starting with 2008, dots are OK in a string representation of a hierarchyid column, so we don't need to replace them with slashes. A quote from the doc:

Comparison is performed by comparing the integer sequences separated by dots in dictionary order.

There's one caveat though: the version segments must not be prefixed with zeroes.

Answer 5

In M$ SQL I had issues with hierachyid with some data...

select Convert(hierarchyid, '/' + '8.3.0000.1088' + '/')

To get around this I used pasename (relies on '.' being the separator)...

Order by
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 1))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 2))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 3))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 4))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 5)))

Answer 6

In PostgreSQL you can do:

SELECT * FROM Requirements
ORDER BY string_to_array(version, '.')::int[];

This last ::int[] makes it convert string values into integers and then compare as such.

Answer 7

FİXED THİS WAY.

<pre>
00000001    1
00000001.00000001   1.1
00000001.00000001.00000001  1.1.1
00000001.00000002   1.2
00000001.00000009   1.9
00000001.00000010   1.10
00000001.00000011   1.11
00000001.00000012   1.12
00000002    2
00000002.00000001   2.1
00000002.00000001.00000001  2.1.1
00000002.00000002   2.2
00000002.00000009   2.9
00000002.00000010   2.10
00000002.00000011   2.11
00000002.00000012   2.12

select * from (select '000000001' as tCode,'1' as Code union
select '000000001.000000001' as tCode,'1.1'as Code union
select '000000001.000000001.000000001' as tCode,'1.1.1'as Code union
select '000000001.000000002' as tCode,'1.2'  union
select '000000001.000000010' as tCode,'1.10'as Code union
select '000000001.000000011' as tCode,'1.11'as Code union
select '000000001.000000012' as tCode,'1.12'as Code union
select '000000001.000000009' as tCode,'1.9' as Code
union
select '00000002' as tCode,'2'as Code union
select '00000002.00000001' as tCode,'2.1'as Code union
select '00000002.00000001.00000001' as tCode,'2.1.1'as Code union
select '00000002.00000002' as tCode,'2.2'as Code union
select '00000002.00000010' as tCode,'2.10'as Code union
select '00000002.00000011' as tCode,'2.11'as Code union
select '00000002.00000012' as tCode,'2.12'as Code union
select '00000002.00000009' as tCode,'2.9'as Code ) as t
order by t.tCode

</pre>

<pre>


public static string GenerateToCodeOrder(this string code)
    {
        var splits = code.Split('.');
        var codes = new List<string>();
        foreach (var str in splits)
        {
            var newStr = "";
            var zeroLength = 10 - str.Length;
            for (int i = 1; i < zeroLength; i++)
            {
                newStr += "0";
            }
            newStr += str;
            codes.Add(newStr);
        }
        return string.Join(".", codes);
    }

</pre>

Answer 8

On PostgreSQL, it couldn't be easier:

SELECT ver_no FROM version ORDER BY string_to_array(ver_no, '.', '')::int[]

Answer 9

Here's a comparison function for PostgreSQL that will compare arbitrary strings such that sequences of digits are compared numerically. In other words, "ABC123" > "ABC2", but "AB123" < "ABC2". It returns -1, 0, or +1 as such comparison functions usually do.

CREATE FUNCTION vercmp(a text, b text) RETURNS integer AS $$
DECLARE
   ar text[];
   br text[];
   n integer := 1;
BEGIN
   SELECT array_agg(y) INTO ar FROM (SELECT array_to_string(regexp_matches(a, E'\\d+|\\D+|^$', 'g'),'') y) x;
   SELECT array_agg(y) INTO br FROM (SELECT array_to_string(regexp_matches(b, E'\\d+|\\D+|^$', 'g'),'') y) x;
   WHILE n <= array_length(ar, 1) AND n <= array_length(br, 1) LOOP
      IF ar[n] ~ E'^\\d+$' AND br[n] ~ E'^\\d+$' THEN
         IF ar[n]::integer < br[n]::integer THEN
            RETURN -1;
         ELSIF ar[n]::integer > br[n]::integer THEN
            RETURN 1;
         END IF;
      ELSE
         IF ar[n] < br[n] THEN
            RETURN -1;
         ELSIF ar[n] > br[n] THEN
            RETURN 1;
         END IF;
      END IF;
      n := n + 1;
   END LOOP;

   IF n > array_length(ar, 1) AND n > array_length(br, 1) THEN
      RETURN 0;
   ELSIF n > array_length(ar, 1) THEN
      RETURN 1;
   ELSE
      RETURN -1;
   END IF;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

You can then create an operator class so that sorting can be done by using the comparison function with ORDER BY field USING <#:

CREATE OR REPLACE FUNCTION vernum_lt(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) < 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_lte(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) <= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_eq(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) = 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_gt(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) > 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_gte(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) >= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OPERATOR <# ( PROCEDURE = vernum_lt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ># ( PROCEDURE = vernum_gt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR =# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR <=# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR >=# ( PROCEDURE = vernum_gte, LEFTARG = text, RIGHTARG = text);

CREATE OPERATOR CLASS vernum_ops FOR TYPE varchar USING btree AS
  OPERATOR 1 <# (text, text),
  OPERATOR 2 <=# (text, text),
  OPERATOR 3 =#(text, text),
  OPERATOR 4 >=# (text, text),
  OPERATOR 5 ># (text, text),
  FUNCTION 1 vercmp(text, text)
;

Answer 10

Just remove the dots (Inline, replace with empty string) cast the result as int and order by the result. Works great:

a.Version = 1.4.18.14

select...
Order by cast( replace (a.Version,'.','') as int) 

Answer 11

NOT USİNG CODE

Insert into @table
Select 'A1' union all
Select 'A3' union all
Select 'A5' union all
Select 'A15' union all
Select 'A11' union all
Select 'A10' union all
Select 'A2' union all
Select 'B2' union all
Select 'C2' union all
Select 'C22' union all
Select 'C221' union all
Select 'A7' 

Select cod from @table
Order by LEN(cod),cod 

Result :

A1
A2
A3
A5
A7
B2
C2
A10
A11
A15
C22
C221

It's simple as:

Declare @table table(id_ int identity(1,1), cod varchar(10))

Insert into @table
Select 'A1' union all
Select 'A3' union all
Select 'A5' union all
Select 'A15' union all
Select 'A11' union all
Select 'A10' union all
Select 'A2' union all
Select 'A7' 

Select cod from @table
Order by LEN(cod),cod  

Answer 12

SELECT * FROM Requirements 
WHERE Requirements.Release NOT LIKE '%Obsolete%' 
ORDER BY cast('/' + replace(Requirements.ReqNum , '.', '/') + '/' as hierarchyid);

Answer 13

Function for PostgreSQL

Simply use

select *
  from sample_table
 order by _sort_version(column_version);




CREATE FUNCTION _sort_version (
  p_version text
)
RETURNS text AS
$body$
declare 
  v_tab text[];
begin
  v_tab := string_to_array(p_version, '.');  

  for i in 1 .. array_length(v_tab, 1) loop
    v_tab[i] := lpad(v_tab[i], 4, '0');
  end loop;

  return array_to_string(v_tab, '.');
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 1;

Answer 14

The following function will take a version number and format each level out to 3 digits:

Usage:

select * from TableX order by dbo.fn_VersionPad(VersionCol1)

Function:

CREATE FUNCTION [dbo].[fn_VersionPad]
(
    @version varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
    /*
        Purpose:  Pads multi-level Version Number sections to 3 digits
        Example:  1.2.3.4
        Returns:  001.002.003.004
    */

    declare @verPad varchar(20)
    declare @i int
    declare @digits int

    set @verPad = ''

    set @i = len(@version)
    set @digits = 0

    while @i > 0
    begin
        if (substring(@version, @i, 1) = '.')
        begin
            while (@digits < 3)
            begin
                -- Pad version level to 3 digits
                set @verPad = '0' + @verPad
                set @digits = @digits + 1
            end

            set @digits = -1
        end

        set @verPad = substring(@version, @i, 1) + @verPad

        set @i = @i - 1
        set @digits = @digits + 1
    end

    while (@digits < 3)
    begin
        -- Pad version level to 3 digits
        set @verPad = '0' + @verPad
        set @digits = @digits + 1
    end

    return @verPad
END

Answer 15

If you are in SQL Server land...

DECLARE @string varchar(40)
SET @string = '1.2.3.4'
SELECT PARSENAME(@string, 1), PARSENAME(@string, 2), PARSENAME(@string, 3), PARSENAME(@string, 4)

Results: 4, 3, 2, 1

Useful for parsing IP Addresses and other dotted items, such as a version number. (You can use REPLACE() to convert items into dotted notation too... e.g. 1-2-3-4 -> 1.2.3.4)

Answer 16

For the all-in-one-query purists, assuming Oracle, some instr/substr/decode/to_number voodoo can solve it:

SELECT *
FROM Requirements
WHERE Release NOT LIKE '%Obsolete%'
ORDER BY
    to_number(
      substr( reqnum, 1, instr( reqnum, '.' ) - 1 )
    )
  , to_number(
      substr( 
          reqnum
        , instr( reqnum, '.' ) + 1 -- start: after first occurance
        , decode( 
              instr( reqnum, '.', 1, 2 )
            , 0, length( reqnum )
            , instr( reqnum, '.', 1, 2 ) - 1 
          ) -- second occurance (or end)
          - instr( reqnum, '.', 1, 1) -- length: second occurance (or end) less first
      )
    )
  , to_number(
      decode( 
          instr( reqnum, '.', 1, 2 )
        , 0, null
        , substr( 
              reqnum
            , instr( reqnum, '.', 1, 2 ) + 1 -- start: after second occurance
            , decode( 
                  instr( reqnum, '.', 1, 3 )
                , 0, length( reqnum )
                , instr( reqnum, '.', 1, 3 ) - 1 
              ) -- third occurance (or end)
              - instr( reqnum, '.', 1, 2) -- length: third occurance (or end) less second
          ) 
      )
    )
  , to_number(
      decode( 
          instr( reqnum, '.', 1, 3 )
        , 0, null
        , substr( 
              reqnum
            , instr( reqnum, '.', 1, 3 ) + 1 -- start: after second occurance
            , decode( 
                  instr( reqnum, '.', 1, 4 )
                , 0, length( reqnum )
                , instr( reqnum, '.', 1, 4 ) - 1 
              ) -- fourth occurance (or end)
              - instr( reqnum, '.', 1, 3) -- length: fourth occurance (or end) less third
          ) 
      )
    )
;

I suspect there are plenty of caveats including:

• assumption of the presence of minor version (second)
• limited to four versions as specified in question's comments

Answer 17

Ok, if high performance is an issue then your only option is to change your values into something numeric.

However, if this is a low usage query then you can just split your numbers and order by those.

This query assumes just major and minor version numbers and that they contain just numbers.

SELECT
    *
FROM
    Requirements
WHERE
    Requirements.Release NOT LIKE '%Obsolete%'
ORDER BY
    CONVERT(int, RIGHT(REPLICATE('0', 10) + LEFT(Requirements.ReqNum, CHARINDEX('.', Requirements.ReqNum)-1), 10)),
    CONVERT(int, SUBSTRING(Requirements.ReqNum, CHARINDEX('.', Requirements.ReqNum )+1, LEN(Requirements.ReqNum) - CHARINDEX('.', Requirements.ReqNum )))

Answer 18

This would work if you're using Microsoft SQL Server:

create function fnGetVersion (@v AS varchar(50)) returns bigint as
begin
declare @n as bigint;
declare @i as int;
select @n = 0;
select @i = charindex('.',@v);
while(@i > 0)
begin
    select @n = @n * 1000;
    select @n = @n + cast(substring(@v,1,@i-1) as bigint); 
    select @v = substring(@v,@i+1,len(@v)-@i);
    select @i = charindex('.',@v);
end
return @n * 1000 + cast(@v as bigint);
end

Test by running this command:

select dbo.fnGetVersion('1.2.3.4')

That would return the number 1002003004 wich is perfectly sortable. Is you need 9.0.1 to be bigger than 2.1.2.3 then you would need to change the logic slightly. In my example 9.0.1 would be sorted before 2.1.2.3.

Answer 19

Here is an example query that extracts the string. You should be able to use this in either the UPDATE refactoring of the database, or simply in your query as-is. However, I'm not sure how it is on time; just something to watch out and test for.

SELECT SUBSTRING_INDEX("1.5.32",'.',1) AS MajorVersion,
  SUBSTRING_INDEX(SUBSTRING_INDEX("1.5.32",'.',-2),'.',1) AS MinorVersion,
  SUBSTRING_INDEX("1.5.32",'.',-1) AS Revision;

this will return:

MajorVersion | MinorVersion | Revision
1            | 5            | 32

Answer 20

I've had the same problem, though mine was with apartment numbers like A1, A2, A3, A10, A11, etc, that they wanted to sort "right". If splitting up the version number into separate columns doesn't work, try this PL/SQL. It takes a string like A1 or A10and expands it into A0000001, A0000010, etc, so it sorts nicely. Just call this in ORDER BY clause, like

select apt_num from apartment order by PAD(apt_num)

function pad(inString IN VARCHAR2)
   return VARCHAR2

--This function pads the numbers in a alphanumeric string.
--It is particularly useful in sorting, things like "A1, A2, A10"
--which would sort like "A1, A10, A2" in a standard "ORDER BY name" clause
--but by calling "ORDER BY pkg_sort.pad(name)" it will sort as "A1, A2, A10" because this
--function will convert it to "A00000000000000000001, A00000000000000000002, A00000000000000000010" 
--(but since this is in the order by clause, it will
--not be displayed.

--currently, the charTemplate variable pads the number to 20 digits, so anything up to 99999999999999999999 
--will work correctly.
--to increase the size, just change the charTemplate variable.  If the number is larger than 20 digits, it will just
--appear without padding.


   is
      outString VARCHAR2(255);
      numBeginIndex NUMBER;
      numLength NUMBER;
      stringLength NUMBER;
      i NUMBER;
      thisChar VARCHAR2(6);
      charTemplate VARCHAR2(20) := '00000000000000000000';
      charTemplateLength NUMBER := 20;


   BEGIN
      outString := null;
      numBeginIndex := -1;
      numLength := 0;
      stringLength := length(inString);

      --loop through each character, get that character
      FOR i IN 1..(stringLength) LOOP
         thisChar := substr(inString, i, 1);

         --if this character is a number
         IF (FcnIsNumber(thisChar)) THEN

            --if we haven't started a number yet
            IF (numBeginIndex = -1) THEN
               numBeginIndex := i;
               numLength := 1;

            --else if we're in a number, increase the length
            ELSE 
               numLength := numLength + 1;
            END IF;

            --if this is the last character, we have to append the number
            IF (i = stringLength) THEN
               outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
            END IF;

         --else this is a character
         ELSE

            --if we were previously in a number, concat that and reset the numBeginIndex
            IF (numBeginIndex != -1) THEN
               outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
               numBeginIndex := -1;
               numLength := 0;
            END IF;

            --concat the character
            outString := outString || thisChar;
         END IF;
      END LOOP;

      RETURN outString;

   --any exception, just return the original string
   EXCEPTION WHEN OTHERS THEN
      RETURN inString;

   END;     

Answer 21

You could split up the string (you already know the delimiters: ".") with CHARINDEX / SUBSTR and ORDER BY the different parts. Do it in a function or do it part by part.

It won't be pretty and it won't be fast: so if you need fast queries, follow Tony or Joel.

Answer 22

I would do as Joel Coehoorn said. Then to re-arrange your data structure you don't have to manually do it. You can write a simple script that will do the job for all 600 records.

Answer 23

If you don't re-design the table as Joel Coehoorn sensibly suggests, then you need to re-format the version numbers to a string that sorts as you require, e.g.

• 1.1 -> 0001.0001.0000
• 162.1.11 -> 0162.0001.0011

This could be done by a function, or using a computed/virtual column if your DBMS has these. Then you can use that function or column in the ORDER BY clause.

Answer 24

For best results, refactor version number storage so that each section has it's own column: MajorVersion, MinorVersion, Revision, Build. Then the ordering problem suddenly becomes trivial. You can also build a computed column for easy retrieval of the full string.