Solved

how to sort value

Posted on 2015-01-07
8
91 Views
Last Modified: 2015-01-19
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 ??
0
Comment
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40535131
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
 
LVL 32

Expert Comment

by:awking00
ID: 40536030
Where do these values exist (e.g. in an array or a list or a database table)?
0
 
LVL 20

Author Comment

by:Sathish David Kumar N
ID: 40537096
actually its DB value  . so we can do it what ever we want ?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40537273
you can split (and orderby) in the database, wes
if you want specific code, you need to clarify which database you are using...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 40537322
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 40537810
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539613
well, this gives the question on the actual requirements...
anyhow, I see AA1 in the input, but only A1 in the output...
0
 
LVL 32

Expert Comment

by:awking00
ID: 40540181
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
more than one jdk and one jre 1 50
factorial example 4 49
fibonacci ten numbers 4 45
arguments to jar 5 35
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues undeā€¦
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilationā€¦
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question