CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Gopal
Gopal

Reputation: 11982

How to order the numbers?

Table1

id value
---------    
 1  100
2A  200
 2  300
10  500
 8  200
....

Select * 
from table1 
order by id

Showing output as

id value
------------    
 1 100
10 500
2A 200
 2 300
 8 200
....

How to make a proper order?

Expected output

id value
----------
 1 100
 2 300
2A 200
 8 200
10 500
....

Upvotes: 1

Views: 88

Answers (5)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

CREATE FUNCTION [dbo].[RemoveNonAlphaCharacters](@Temp varchar(1000))
RETURNS int
AS
BEGIN
  WHILE PatIndex ('%[^0-9]%', @Temp) > 0
  SET @Temp = Stuff( @Temp, PatIndex('%[^0-9]%' , @Temp ), 1, '')
  RETURN @Temp
END

SELECT id, value
FROM dbo.Table1
ORDER BY [dbo].[RemoveNonAlphaCharacters](id) ASC

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

If it is fixed that last character may be character then you can try following query

 WITH A(ID) 
 AS
 (
 SELECT '1'
 UNION ALL
 SELECT '2C'
 UNION ALL
 SELECT '2A'
 UNION ALL
 SELECT '2'
 UNION ALL
 SELECT '10'
 )
 SELECT * 
 FROM A
 ORDER BY 
 convert(int,
 Case When IsNumeric(ID) = 0 then left(ID,len(id)-1) 
 Else ID END
 ) , Case When IsNumeric(ID) = 0 then RIGHT(ID,1) Else '0' END

and if it is variable then you can write a function that replace charecter with its ansi value or 0 . and then put order by close on that column .

Upvotes: 1

vyakhir
vyakhir

Reputation: 1793

SELECT * FROM table1 ORDER BY CAST(id as varchar(50))

Upvotes: 0

rene
rene

Reputation: 42483

Select * 
from table1 
order by cast(replace(lower(id), 'abcdefg', '') as int),
         replace(id, '0123456789','');

Upvotes: 0

user906780
user906780

Reputation: 

SELECT
LEFT(ID,1),
RIGHT(ID,1),
* 
FROM table1 
ORDER BY LEFT(ID,1),RIGHT(ID,1)

should do the trick, I'm not even sure if the left and right is needed in the selected statement.

Upvotes: 0

Related Questions