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 */
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 Server converting string to numeric and sorting
I think this is the problem with your data - not all are match your pattern.
You can check what is not valid using:
select column_name from table_name where ISNUMERIC(replace([column_name],'A','')) = 0
SQL Server sort by letters first then numbers
This seems to match your logic:
select *
from tab
order by
case when col not like '%[0-9-]%' then 0 else 1 end -- no digits
,case when col like '[^0-9-]%' then 0 else 1 end -- starting with non-digit
,case when col like '-%' then 0 else 1 end -- negative values first
,len(col) -- shorter (=smaller) values first
,col
See fiddle
How to sort string with numbers in sql redshift in descending order?
The simplest method for your data is to use len()
along with the name:
order by len(name) desc, name desc;
This assumes that the prefix before the numbers is always the same length (and that the numbers are integers that are not zero-padded).
Sorting string column containing numbers in SQL?
Going on the assumption it's always WORD_space_NUMBER this should work:
SELECT *
FROM table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)
Use POSITION to find the space, SUBSTRING to grab the number after it, and CAST to make it a comparable value.
If there is a different pattern to the column, let me know and I'll try to devise a better work-around.
EDIT Proven to work:
mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t ORDER BY st;
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 4 | a 11 |
| 2 | a 12 |
| 3 | a 6 |
+----+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 3 | a 6 |
| 4 | a 11 |
| 2 | a 12 |
+----+------+
mysql> INSERT INTO t (st) VALUES ('b 1'),('b 12'),('b 6'),('b 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 5 | b 1 |
| 3 | a 6 |
| 7 | b 6 |
| 4 | a 11 |
| 8 | b 11 |
| 2 | a 12 |
| 6 | b 12 |
+----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 3 | a 6 |
| 4 | a 11 |
| 2 | a 12 |
| 5 | b 1 |
| 7 | b 6 |
| 8 | b 11 |
| 6 | b 12 |
+----+------+
8 rows in set (0.00 sec)
ignore my lame table/column names, but gives me the correct result. Also went a little further and added double sort to break letters prefix with numeric.
EditSUBSTRING_INDEX
will make it little more readable.
ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)
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 |
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;
Related Topics
Ms Access Displaying Vba Select Query in Datasheet
Problems with Createdb in Postgres
SQL Inner Join Over Multiple Tables Equal to Where Syntax
SQL Query Continues Running for a Very Long Time If Search Term Not Found
Creating a Form Where User Inputs Start and End Dates of a Report
Average Difference Between Two Dates, Grouped by a Third Field
SQL Server Copying Tables from One Database to Another
Querying and Selecting Specific Column in SQLalchemy
Conditional Operator in SQL Where Clause
SQL Transform Crosstab Pivot Data
Using Regular Expression Within a Stored Procedure
Handling Non Existent Values in SQL Query Expression for Ssrs Chart
Get All Punch in and Out for Each Employee
Find Out the Calling Stored Procedure in SQL Server
When How to Use an Identifier Number Instead of Its Name in Postgresql