SQL sort by version number, a string of varying length
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.
Oracle SQL to Sort Version Numbers
This is one way to do it. First order by the number before . and then by the numbers after .
select version_number
from mytable
order by substr(version_number, 1, instr(version_number,'.')-1) desc
,length(substr(version_number, instr(version_number,'.')+1)) desc
,substr(version_number, instr(version_number,'.')+1) desc
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query
If You are using SQL Server 2008
select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);
What is hierarchyid
Edit:
Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.
The challenge
How can I sort a table by a numerical string as a number when this sorted columns results contain all numbers in SQL?
Hmmm. Is this what you want?
SELECT c.*
FROM chemicals c
WHERE ClientID = '9999'
ORDER BY TRY_CONVERT(float, [Client Number]),
[Client Number],
MSDS ASC;
In pre-2012 versions, you can use a case statement instead of try_convert():
SELECT c.*
FROM chemicals c
WHERE ClientID = '9999'
ORDER BY (CASE WHEN ISNUMERIC([Client Number]) = 1 THEN CONVERT(float, [Client Number]) END),
[Client Number],
MSDS ASC;
How to sort version numbers (like 5.3.60.8)
I will show here the answer from AskTom, which can be used with different version size :
WITH inputs
AS (SELECT 1 as id, '6.0.5.94' as col FROM DUAL
UNION ALL
SELECT 2,'5.3.30.8' FROM DUAL
UNION ALL
SELECT 3,'5.3.4.8' FROM DUAL
UNION ALL
SELECT 4,'3' FROM DUAL
UNION ALL
SELECT 5,'3.3.40' FROM DUAL
UNION ALL
SELECT 6,'3.3.4.1.5' FROM DUAL
UNION ALL
SELECT 7,'3.3.4.1' FROM DUAL)
SELECT col, MAX (SYS_CONNECT_BY_PATH (v, '.')) p
FROM (SELECT t.col, TO_NUMBER (SUBSTR (x.COLUMN_VALUE, 1, 5)) r, SUBSTR (x.COLUMN_VALUE, 6) v, id rid
FROM inputs t,
TABLE (
CAST (
MULTISET (
SELECT TO_CHAR (LEVEL, 'fm00000')
|| TO_CHAR (TO_NUMBER (SUBSTR ('.' || col || '.', INSTR ('.' || col || '.', '.', 1, ROWNUM) + 1, INSTR ('.' || col || '.', '.', 1, ROWNUM + 1) - INSTR ('.' || col || '.', '.', 1, ROWNUM) - 1)), 'fm0000000000')
FROM DUAL
CONNECT BY LEVEL <= LENGTH (col) - LENGTH (REPLACE (col, '.', '')) + 1) AS SYS.odciVarchar2List)) x)
START WITH r = 1
CONNECT BY PRIOR rid = rid AND PRIOR r + 1 = r
GROUP BY col
ORDER BY p
Sort string and number combination in descending in oralce
You did not actually explain how the strings should turned to numbers.
This would work for your dataset:
order by to_number(regexp_replace(firmware, '\D', '')) desc
The idea is to remove all non-digits characters from the string, turn the resulting string to a number, and use it for sorting.
with inputs (firmware) as (
select 'P9S1' from dual union all
select 'P7S1' from dual union all
select 'P13S1' from dual union all
select 'P12S2' from dual union all
select 'P10S1' from dual
)
select firmware
from inputs
order by to_number(regexp_replace(firmware, '\D', '')) desc ;
| FIRMWARE |
| :------- |
| P13S1 |
| P12S2 |
| P10S1 |
| P9S1 |
| P7S1 |
Sort string as number in sql server
Judicious use of REVERSE, CHARINDEX, and SUBSTRING, can get us what we want. I have used hopefully-explanatory columns names in my code below to illustrate what's going on.
Set up sample data:
DECLARE @Invoice TABLE (
InvoiceNumber nvarchar(10)
);
INSERT @Invoice VALUES
('790711')
,('790709-1')
,('790709-11')
,('790709-21')
,('790709-212')
,('790709-2')
SELECT * FROM @Invoice
Sample data:
InvoiceNumber
-------------
790711
790709-1
790709-11
790709-21
790709-212
790709-2
And here's the code. I have a nagging feeling the final expressions could be simplified.
SELECT
InvoiceNumber
,REVERSE(InvoiceNumber)
AS Reversed
,CHARINDEX('-',REVERSE(InvoiceNumber))
AS HyphenIndexWithinReversed
,SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))
AS ReversedWithoutAffix
,SUBSTRING(InvoiceNumber,1+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixIncludingHyphen
,SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixExcludingHyphen
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
AS AffixAsInt
,REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
AS WithoutAffix
FROM @Invoice
ORDER BY
-- WithoutAffix
REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
-- AffixAsInt
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
Output:
InvoiceNumber Reversed HyphenIndexWithinReversed ReversedWithoutAffix AffixIncludingHyphen AffixExcludingHyphen AffixAsInt WithoutAffix
------------- ---------- ------------------------- -------------------- -------------------- -------------------- ----------- ------------
790709-1 1-907097 2 907097 -1 1 1 790709
790709-2 2-907097 2 907097 -2 2 2 790709
790709-11 11-907097 3 907097 -11 11 11 790709
790709-21 12-907097 3 907097 -21 21 21 790709
790709-212 212-907097 4 907097 -212 212 212 790709
790711 117097 0 117097 0 790711
Note that all you actually need is the ORDER BY clause, the rest is just to show my working, which goes like this:
- Reverse the string, find the hyphen, get the substring after the hyphen, reverse that part: This is the number without any affix
- The length of (the number without any affix) tells us how many characters to drop from the start in order to get the affix including the hyphen. Drop an additional character to get just the numeric part, and convert this to
int. Fortunately we get a break from SQL Server in that this conversion gives zero for an empty string. - Finally, having got these two pieces, we simple
ORDER BY(the number without any affix) and then by (the numeric value of the affix). This is the final order we seek.
The code would be more concise if SQL Server allowed us to say SUBSTRING(value, start) to get the string starting at that point, but it doesn't, so we have to say SUBSTRING(value, start, LEN(value)) a lot.
SQL order string as number
If possible you should change the data type of the column to a number if you only store numbers anyway.
If you can't do that then cast your column value to an integer explicitly with
select col from yourtable
order by cast(col as unsigned)
or implicitly for instance with a mathematical operation which forces a conversion to number
select col from yourtable
order by col + 0
BTW MySQL converts strings from left to right. Examples:
string value | integer value after conversion
--------------+--------------------------------
'1' | 1
'ABC' | 0 /* the string does not contain a number, so the result is 0 */
'123miles' | 123
'$123' | 0 /* the left side of the string does not start with a number */
Related Topics
Postgresql Join with Array Type with Array Elements Order, How to Implement
How to Delete the Top 1000 Rows from a Table Using SQL Server 2008
How to Use a Dynamic Parameter in a in Clause of a JPA Named Query
Eliminate and Reduce Overlapping Date Ranges
How in H2Db Get SQL Dump Like in MySQL
Why Are Aggregate Functions Not Allowed in Where Clause
SQL - Call Stored Procedure for Each Record
Sqlite Order by Date1530019888000
SQL Populate Table with Random Data
Get Count of Records Affected by Insert or Update in Postgresql
Zero SQL Deadlock by Design - Any Coding Patterns
Previous Monday & Previous Sunday's Date Based on Today's Date
Tsql - How to Use Go Inside of a Begin .. End Block
MySQL Convert Latin1 Data to Utf8
How to Group Timestamps into Islands (Based on Arbitrary Gap)