how to sort value

Hi i have
value like

A10
B100
A2
AA1
AD200
32
11
A1

and the i want the result should be
A1
A2
A10
B2
B100
AD200
11
32

is that possible ??
LVL 20
Sathish David  Kumar NArchitectAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I have written this short article (still in "draft", hence not yet available) about this issue with code examples in MS SQL Server and Oracle:
http://www.experts-exchange.com/Database/Miscellaneous/A_17544-ORDER-BY-a-combined-value-text-numercial.html

the code example for Oracle:
select value
, regexp_substr(value, '([^0-9]*)') prefix
, to_number(substr(value, nvl( 1+length(regexp_substr(value, '([^0-9]*)')),1),length(value))) numvalue
from my_ee 
order by prefix, numvalue;

Open in new window

the code example for ms sql server:
select value
, left(value, patindex('%[0-9]%', value) -1 ) prefix
, cast(substring(value, patindex('%[0-9]%', value) , len(value)) as int) numvalue
from my_ee 
order by prefix, numvalue

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to sort by first the alpha/string part, and then by the numerical part.
so, you need to split the string accordingly, and apply the sorting based on the string and in case of equality of that by the numerical value behind
usually, the real issue behind is that the 2 parts should be really 2 parts, and not combined in the database behind, and only combined for the front-end if/as needed

I am not 100% sure about java code, just telling the general approach on how to do it and how to consider the "step back"
0
 
awking00Commented:
Where do these values exist (e.g. in an array or a list or a database table)?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Sathish David Kumar NArchitectAuthor Commented:
actually its DB value  . so we can do it what ever we want ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can split (and orderby) in the database, wes
if you want specific code, you need to clarify which database you are using...
0
 
awking00Connect With a Mentor Commented:
Slight variation for Oracle -
select value,
 regexp_replace(value,'[0-9]') alpha,
 to_number(regexp_replace(value,'[A-Z]')) numeric
 from yourtable
order by length(alpha),alpha, numeric;

Note - length is required to sort AD200 after B100, same will hold true for SQL Server using len() function.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, this gives the question on the actual requirements...
anyhow, I see AA1 in the input, but only A1 in the output...
0
 
awking00Commented:
I agree that the sample input and output do not match. 'AA1' is in the input but not the output and 'B2' is in the output but not the input. Regardless of what the input is or should be, the use of a length function sorts the alpha portion with the order of:
A, B, C, ..., Z, AA, AB, AC, ..., AZ, BA, BB, BC, ..., BZ, ... etc. until it reaches ZZ. (or a string of Zs for the maximum number of characters in the alpha portion of the value), making, for example, AD200 sort after B100 since AD would come after B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.