How can I use SQL to Order By for version numbers (strings)?

Question

Hello everyone! 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:

$bQuery = "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
etc.

How can I write a query that will sort by lexicographic order? or How can I sort the data I receive to be in the proper order?

Thanks for the input in advance!

Answer 1

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.

Answer 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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)