12,356,331 members (66,579 online)
Rate this:
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 8-Oct-12 1:29am

Rate this:

Rate this:

## 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
Rate this:

## 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```
Rate this:

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

Top Experts
Last 24hrsThis month
 OriginalGriff 320 ErBhati 195 Richard Deeming 175 Sergey Alexandrovich Kryukov 173 Karthik Bangalore 115
 OriginalGriff 9,958 Sergey Alexandrovich Kryukov 6,606 Dave Kreskowiak 3,269 ppolymorphe 2,441 Karthik Bangalore 2,381