Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
Select Number from tableName order by Number
 
it displays like below
 
1
 
10
 
11
 
14
 
14A
 
14AA
19
 
2
 
20
 
21
Instead it should display like below
 
1
 
2
 
10
 
11
 
14
 
14A
 
14AA
19
 
20
Posted 8-Oct-12 1:29am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
See the below query. It will give you desired output.
 
<pre lang="sql">select SrNo from Table_1
order by case
when SrNo like '%[0-9]%' then 1
else 0
end,
SrNo
 
Here SrNo is your table column name.
 
Thanks,
Viprat
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
Select Row_Number() Over (Order by
Case When IsNumeric(User_Name) = 1 then Right(Replicate('0',21) + User_Name, 20)
                          When IsNumeric(User_Name) = 0 then Left(User_Name + Replicate('',21), 20)
                        Else User_Name
               END) As RowNumber,User_Name
From Users
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi,
 
Sorting Alpha numeric data is bit difficult one. But it can be done by following method..
 
 
  SELECT value from YourTable Order By 
REPLACE(value, LEFT(SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000), 
PATINDEX('%[^0-9.-]%', SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000) + 'X')-1),
REPLICATE('0', 20 - len(LEFT(SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000), PATINDEX('%[^0-9.-]%',
SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000) + 'X')-1))+LEFT(SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(value, PATINDEX('%[0-9.-]%', value), 8000) + 'X')-1)))
 

 
Thank you...
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 550
1 Sergey Alexandrovich Kryukov 420
2 George Jonsson 288
3 Pheonyx 218
4 OriginalGriff 200
0 OriginalGriff 4,813
1 CPallini 3,850
2 Sergey Alexandrovich Kryukov 3,224
3 George Jonsson 2,552
4 Gihan Liyanage 2,186


Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 9 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100