Solved

how to sort value

Posted on 2015-01-07
8
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

696 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