Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Red face Unanswered: report data sorting

    hi all

    how to sort data in oracle report order by name.'Mr' is present before the name. example Mr. John Mr. Smith
    Plz help me

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by oryazi View Post
    hi all

    how to sort data in oracle report order by name.'Mr' is present before the name. example Mr. John Mr. Smith
    Plz help me :eek:
    Hi,
    your post is a little unclear. 'Mr' or 'Mr. '? If it is present always, order by name itself - it does not have effect on ordering. If only some names contain it, use REGEXP_REPLACE to replace it selectively.
    Code:
    SQL> with tbl_name as (select 'Mr. John' nam from dual union all
      2                    select 'Mr. Smith' from dual union all
      3                    select 'Peters' nam from dual )
      4  select nam, regexp_replace( nam, '^Mr. ' ) nam_mod
      5  from tbl_name
      6  order by regexp_replace( nam, '^Mr. ' );
    
    NAM       NAM_MOD
    --------- ---------
    Mr. John  John
    Peters    Peters
    Mr. Smith Smith
    
    3 rows selected.
    
    SQL>

Posting Permissions

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