Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Unanswered: Ascending order in numeric with char in db2

    Hi,

    How can i numerical order in db2 database. for example i am using that format for

    Field1 Filed2
    20AC PRO
    299AC PRO
    322AC PRO
    49AC PRO
    5AC PRO

    I would like to receive the numbers in the following order:

    Field1 Filed2
    5AC PRO
    20AC PRO
    49AC PRO
    299AC PRO
    322AC PRO


    pls help?

    Thanks

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    order by 
    xmlcast(
    xmlquery('fn:replace($s, "(\d+).*", "$1")' passing Field1 as "s")
    as int)
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tamilselvantms View Post
    Hi,

    How can i numerical order in db2 database. for example i am using that format for

    Field1 Filed2
    20AC PRO
    299AC PRO
    322AC PRO
    49AC PRO
    5AC PRO

    I would like to receive the numbers in the following order:

    Field1 Filed2
    5AC PRO
    20AC PRO
    49AC PRO
    299AC PRO
    322AC PRO


    pls help?

    Thanks
    If the column always ends with 2 characters, you can remove them and cast what remains to an int:

    Code:
    with t(s) as (
        values '20AC', '299AC', '322AC','49AC','5AC'
    )
    select s
    from t
    order by cast(substr(s,1,length(s)-2) as int);
    --
    Lennart

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What were the all possible format of Field1?

    Order by the results of "removing alphabets and retaining number part only, then cast to numeric".

    Example 1: Very simple case(If the format of field1 was like "nnnXX". last two characters were alphabet).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     t
    (Field1 , Filed2) AS (
    VALUES
      ( '20AC'  , 'PRO' )
    , ( '299AC' , 'PRO' )
    , ( '322AC' , 'PRO' )
    , ( '49AC'  , 'PRO' )
    , ( '5AC'   , 'PRO' )
    )
    SELECT *
     FROM  t
     ORDER BY
           INT( LEFT(Field1 , LENGTH(Field1) - 2) )
    ;
    ------------------------------------------------------------------------------
    
    FIELD1 FILED2
    ------ ------
    5AC    PRO   
    20AC   PRO   
    49AC   PRO   
    299AC  PRO   
    322AC  PRO   
    
      5 record(s) selected.

  5. #5
    Join Date
    Aug 2013
    Posts
    6
    Thanks for all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •