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 */
T-SQL ORDER BY number and letters mixed in string
I'm stealing my details from Here.
declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'
select
left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr
,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t
which results in
122345684 XT
23339034300 -XT
422222222111111111232
423432424523242332 X
423842389034209 XYZ
ABC
To use it in your context.
SELECT *
FROM YourTable
ORDER BY left(s,patindex('%[^0-9]%',S+' ')-1 ),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
Shown by
declare @t table(s varchar(25))
insert @t
select '12X' union
select '1X' union
select '2X' union
select '2Y' union
select '20' union
select '21'
SELECT *
FROM @t
ORDER BY CAST(left(s,patindex('%[^0-9]%',S+' ')-1 ) AS INT),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
Results In
1X
2X
2Y
12X
20
21
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 by string, numbers appear last
You can use regexp_like()
. For the ascending sort:
order by (case when regexp_like(col, '^[0-9]+$') then 1 else 2 end) asc,
col asc
ORDER BY number and letter in one string(Access - SQL)
This should cause numeric values up to 99 to be sorted first.
order by IIF( isnumeric( Mid(tbl_Projektphasen.Bezeichnung, 1, 1) ) or isnumeric( Mid(tbl_Projektphasen.Bezeichnung, 1, 2) ), 1, 2), tbl_Projektphasen.Bezeichnung
Explaination: If the first character or first two characters are numeric, give it an initial sorting value of 1 - otherwise, give it a sorting value of 2. Then sort by the actual field in full.
order by a string as number sql
It's doing a string (alpha) sort, and you actually want to be doing a numeric sort.
I'd change the order line from:
order by WeeksBand
to:
order by Cast(SubString(WeeksBand, 1, CASE WHEN CHARINDEX('-', WeeksBand) != 0 THEN CHARINDEX('-', WeeksBand) -1 ELSE LEN(WeeksBand) END) AS INT)
which will convert the first number into an integer value (not string) and then should order correctly.
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
Related Topics
Generate_Series() Equivalent in MySQL
Update Multiple Rows in Same Query Using Postgresql
What Are Valid Table Names in Sqlite
Using the Result of an Expression (E.G. Function Call) in a Stored Procedure Parameter List
SQL Rank() Versus Row_Number()
Pass Multiple Values in Single Parameter
Using an Alias in SQL Calculations
SQL Order by Multiple Values in Specific Order
MySQL Search and Replace Some Text in a Field
How to Do Pagination in SQL Server 2008
Copy Tables from One Database to Another in SQL Server
Generate a Resultset of Incrementing Dates in Tsql
What Are the Most Common SQL Anti-Patterns
SQL Server - Transactions Roll Back on Error
Unrecognized Name: Employees At [9:8]
How to Rewrite Is Distinct from and Is Not Distinct from in SQL Server 20008R2