sorting of alphanumeric data

Question

0.00/5 (No votes)

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

aashi310

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

vivektiwari97701 · Answer 1 · 2012-10-08T02:11:00

Solution 1

How to Sort Alphanumeric Data in SQL[^]

http://stackoverflow.com/questions/8148778/sorting-alphanumeric-data[^]

Posted 8-Oct-12 2:11am

vivektiwari97701

VIPR@T · Answer 2 · 2012-10-08T02:22:00

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

Posted 8-Oct-12 2:22am

VIPR@T

Rahul K27 · Answer 3 · 2012-10-08T02:35:00

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

damodara naidu betha · Answer 4 · 2012-10-08T20:21:00

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

sorting of alphanumeric data

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0