15,303,572 members
See more:
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

## Solution 2

Hi,

See the below query. It will give you desired output.

SQL
<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

## 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

## Solution 4

Hi,

Sorting Alpha numeric data is bit difficult one. But it can be done by following method..

SQL
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...