How to Sort numeric values mixed with alphanumeric values in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi All,

I have to sort a column contains value like

AZC1
AZC10
AZC12
AZC100
AZC2
AZC24
AZC3
AZC34

My required output is

VB

AZC1
AZC2
AZC3
AZC10
AZC12
AZC24
AZC34
AZC100

(ie) It should sort Alpha values first and then numeris values. Numeric values should be sorted like 1,2,3,10,24....

Posted 10-Jun-14 20:36pm

kirthiga S

Add a Solution

Comments

Thava Rajan 11-Jun-14 7:39am

is first three characters are always alphabets?
if it contains data like this
az1
az2
azc1
azc2
then it is almost a complex query
other wise it is a simple query

2 solutions

Solution 4

ok i gave a simple solution

SQL

select * from test
order by substring(Zone,1,3) ,
cast(STuff(Zone,1,3,'000')  as int)

ok here is a complex one

SQL

select * from test
order by substring(Zone,1,patindex('%[0-9]%',zone)-1) ,
cast(STuff(Zone,1,patindex('%[0-9]%',zone)-1,'000')  as int)

if you want to check it
use this Sql Fiddle[^]

Posted 11-Jun-14 2:32am

Thava Rajan

Updated 11-Jun-14 21:18pm

v2

Comments

Magic Wonder 11-Jun-14 8:50am

Yeah it is simple and sweet but it is limited. What is data contains ABHCDE12 or ABCD1?

Thava Rajan 12-Jun-14 3:36am

well solution updated

Magic Wonder 12-Jun-14 3:40am

Yeah Thats Good.

kirthiga S 12-Jun-14 1:22am

@Thava Rajan

Thank u. Your query is simple and working for my scenario. We can use this for limited case.

Thava Rajan 12-Jun-14 3:36am

solution updated please check it out

Thava Rajan 12-Jun-14 4:22am

mark this as your solution

Thava Rajan 12-Jun-14 3:18am

solution updated please refer it

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)

Peter Leow · Accepted Answer · 2014-06-10T20:43:00

Solution 2

Refer: Sort AlphaNumeric Data in Sql[^]

Posted 10-Jun-14 20:43pm

Peter Leow

Comments

Nandakishore G N 11-Jun-14 8:43am

Good suggestion..My 5.

Thava Rajan 12-Jun-14 3:37am

this will not order the alphabets it only order the numeric parts