12,886,393 members (43,502 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...

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

Top Experts
Last 24hrsThis month
 OriginalGriff 300 CHill60 255 ppolymorphe 140 Maciej Los 140 Jochen Arndt 135
 OriginalGriff 4,297 CHill60 2,643 Karthik Bangalore 2,436 Jochen Arndt 2,248 ppolymorphe 1,985

Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 9 Oct 2012