current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to sort one column fetched from mysql DB, and stored in an array. After fetching I am doing below steps.

  1. DB Fetching fields array in row format. ->Field1, Field2, Field3, Field4, Field5
  2. From that fields array One columns data [Field3], swapping string keywords. eg.
    AB013, DB131, RS001
    should become:
    013AB, 131DB, 001RS

Now I want to sort above value in new string format like-> 001RS, 013AB, 131DB

Its not like that, fix number for swapping string. To split and join string again there is a dynamic value which allow fetch data string to split that string from the given character position and then sort it again with new name.

share|improve this question

2 Answers 2

up vote 0 down vote 
select * from (
  select 
    Field1,
    Field2,
    CASE Field3
      WHEN 'AB013' THEN '013AB'
      WHEN 'DB131' THEN '131DB'
      WHEN 'RS001' THEN '001RS'
    END CASE AS NewField3,
    Field4,
    Field5 
  from Table1 
)
order by NewField3
share|improve this answer
up vote 0 down vote

Why don't you let the DB do the work (for the fixed position data in your example):

select Field1
     , Field2
     , concat(  substr(Field3,3,3) 
               ,substr(Field3,1,2)
             ) as SwapedField3
     , Field4
     , Field5
from Table
order by SwapedField3

However, this doesn't take the 'dynamic value' for the split position into account...

  • Where does that value come from?
  • Can it be deduced from the data somehow?
share|improve this answer
    
In my case there are lots of unique code in string format, which needs to swapping and reorder in list mode. # Now here Unique code & Line code comes from DB, Line code being reference for splitting string & concat string of unique code. # To split string char. no. value also comes from DB and stored with Line code reference. –  Shail Patel May 18 '10 at 12:16
    
@Shail Patel: But couldn't you use that 'line code' to parameterize the "subste()" function, if you use the length() function? something like substr(Field3,LineCode,length(Field3) - LineCode) –  lexu May 19 '10 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.