Solved

how to sort value

Posted on 2015-01-07
8
90 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 142

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
DevOps Toolchain Recommendations

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

 
LVL 142

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 142

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 142

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
egit plugin on eclipse 8 63
Windows 10 and JDK 7 7u85 4 41
tomcat administrtor 12 46
How to determine if a string is a valid SHA value 7 30
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

776 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